Beer Pong: SQL vs PGQL

SQL is in Relational Databases what PGQL is in the Graph Databases world (Oracle-wise). Did you ever think at challenging both solution? Doing the same job, compare how nice, easy, fast it is. Let’s see how it goes…

Transcript

1. View Slide

2. View Slide

3. View Slide

4. Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
   bit.ly/OracleACEProgram
   450+ Technical Experts
   Helping Peers Globally
   Nominate yourself or someone you know: acenomination.oracle.com
   

   View Slide

5. View Slide

6. •
   • (ask Martin Widlake for this one)
   •
   •
   

   View Slide

7. •
   •
   •
   •
   •
   

   View Slide

8. There is a lot more than that, but let’s keep it simple
   

   View Slide

9. Vertex
   edge
   A Property Graph
   (also called node)
   

   View Slide

10. edge
    edge label
    edge properties
    edge ID
    directed edge
    vertex (node)
    vertex
    properties
    vertex ID
    a vertex can
    have a label
    

    View Slide

11. View Slide

12. From 45,700 nodes
    with 105,406 edges,
    to 85 nodes with
    218 edges in
    seconds
    Catalog
    RPD
    

    View Slide

13. Spain
    Italy
    John Doe
    Company A
    Company B
    Company C
    Company D
    Located in
    Located in
    Located in
    Located in
    Buys from
    Buys from
    Buys from
    Buys from
    Money laundering
    and VAT frauds
    Owns
    

    View Slide

14. View Slide

15. Every row of a table has a fixed, identical structure
    Connections at a table level (not row)
    Nodes and edges can have any number of properties
    Connections at a node level (can be seen as row level)
    

    View Slide

16. View Slide

17. View Slide

18. •
    •
    •
    •
    

    View Slide

19. SEGMENT
    CUSTOMER
    ACCOUNT TRANSACTION
    

    View Slide

20. SELECT othercustomer.name, SUM(transaction.amount) as total_transaction
    FROM customer
    INNER JOIN account
    ON customer.id = account.owner
    INNER JOIN transaction
    ON account.id = transaction.src_account OR account.id = transaction.dst_account
    INNER JOIN account otheraccount
    ON (transaction.src_account = otheraccount.id
    OR transaction.dst_account = otheraccount.id)
    AND account.id <> otheraccount.id
    INNER JOIN customer othercustomer
    ON otheraccount.owner = othercustomer.id
    INNER JOIN segment othersegment
    ON othercustomer.segment = othersegment.id
    WHERE customer.name = 'Nikita’
    AND othersegment.name IN ('Person', 'Company')
    GROUP BY othercustomer.name
    

    View Slide

21. View Slide

22. SELECT owner.name, SUM(t.amount) AS total_transaction
    FROM financial_transactions
    MATCH (p:Person) -[:ownerOf]-> (:Account) -[t:transaction]- (:Account) <-
    [:ownerOf]- (owner:Person|Company)
    WHERE p.name = 'Nikita'
    GROUP BY owner
    

    View Slide

23. SELECT owner.name, SUM(t.amount) AS total_transaction
    FROM financial_transactions
    MATCH (p:Person) -[:ownerOf]-> (:Account) -[t:transaction]- (:Account)
    <-[:ownerOf]- (owner:Person|Company)
    WHERE p.name = 'Nikita'
    GROUP BY owner
    

    View Slide

24. SELECT othercustomer.name, SUM(transaction.amount) as total_transaction
    FROM customer
    INNER JOIN account
    ON customer.id = account.owner
    INNER JOIN transaction
    ON account.id = transaction.src_account OR account.id = transaction.dst_account
    INNER JOIN account otheraccount
    ON (transaction.src_account = otheraccount.id
    OR transaction.dst_account = otheraccount.id)
    AND account.id <> otheraccount.id
    INNER JOIN customer othercustomer
    ON otheraccount.owner = othercustomer.id
    INNER JOIN segment othersegment
    ON othercustomer.segment = othersegment.id
    WHERE customer.name = 'Nikita’
    AND othersegment.name IN ('Person', 'Company')
    GROUP BY othercustomer.name
    SELECT owner.name, SUM(t.amount) AS total_transaction
    FROM financial_transactions
    MATCH (p:Person) -[:ownerOf]-> (:Account) -[t:transaction]- (:Account) <-[:ownerOf]-
    (owner:Person|Company)
    WHERE p.name = 'Nikita'
    GROUP BY owner
    

    View Slide

25. View Slide

26. View Slide

27. •
    •
    •
    •
    •
    •
    •
    

    View Slide

28. View Slide

29. View Slide

30. View Slide

31. └
    └
    ID MANAGER
    100
    100
    120
    120
    185
    Steven
    Diana
    Tom
    SELECT id, name, LEVEL
    FROM employees
    CONNECT BY PRIOR id = manager
    

    View Slide

32. (a vertex) –[connected by an edge to]-> (another vertex)
    Steven
    Diana
    Tom
    managedBy
    managedBy
    MATCH (v1)->(v2)
    MATCH (v1)–[e]->(v2)
    MATCH (v1)-(v2)
    MATCH (v1)–[e]-(v2)
    MATCH (v1)<-(v2)
    MATCH (v1)<-[e]-(v2)
    MATCH (v1)-/:label?/->(v2)
    MATCH (v1)-/:label*/->(v2)
    MATCH (v1)-/:label+/->(v2)
    MATCH (v1)-/:label{1,2}/->(v2)
    

    View Slide

33. SELECT id, name, LEVEL
    FROM employees
    CONNECT BY PRIOR id = manager
    SELECT emp.name, mgr.name AS manager
    MATCH (emp:Employee) -/:managedBy*/-> (mgr:Employee)
    

    View Slide

34. View Slide

35. •
    •
    

    View Slide

36. B
    A
    C
    D
    E
    8 min
    5 min
    4 min
    2 min
    7 min
    5 min
    4 min
    Shortest way
    from A to E ?
    

    View Slide

37. SELECT start.name, SUM(e.time) AS distance, dst.name
    MATCH SHORTEST( (start) -[e]->* (dst) )
    WHERE start.name = 'A' AND dst.name = 'E'
    

    View Slide

38. SELECT start.name, SUM(e.time) AS distance, dst.name
    MATCH SHORTEST( (start) -[e]->* (dst) )
    WHERE start.name = 'A' AND dst.name = 'E'
    B
    A
    C
    D
    E
    8 min
    5 min
    4 min
    2 min
    7 min
    5 min
    4 min
    

    View Slide

39. SELECT start.name, SUM(e.time) AS distance, dst.name
    MATCH SHORTEST( (start) -[e]->* (dst) )
    WHERE start.name = 'A' AND dst.name = 'E'
    

    View Slide

40. View Slide

41. View Slide

42. View Slide

43. View Slide

44. View Slide

45. View Slide

46. •
    •
    •
    •
    •
    

    View Slide

47. View Slide

48. View Slide