Beer Pong: SQL vs PGQL

Bf71450537acca19e045ae6f7febdf9a?s=47 Gianni Ceresa
September 07, 2019

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…

Bf71450537acca19e045ae6f7febdf9a?s=128

Gianni Ceresa

September 07, 2019
Tweet

Transcript

  1. None
  2. None
  3. None
  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
  5. None
  6. • • (ask Martin Widlake for this one) • •

  7. • • • • •

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

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

  10. edge edge label edge properties edge ID directed edge vertex

    (node) vertex properties vertex ID a vertex can have a label
  11. None
  12. From 45,700 nodes with 105,406 edges, to 85 nodes with

    218 edges in seconds Catalog RPD
  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
  14. None
  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)
  16. None
  17. None
  18. • • • •

  19. SEGMENT CUSTOMER ACCOUNT TRANSACTION

  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
  21. None
  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
  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
  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
  25. None
  26. None
  27. • • • • • • •

  28. None
  29. None
  30. None
  31. └ └ ID MANAGER 100 100 120 120 185 Steven

    Diana Tom SELECT id, name, LEVEL FROM employees CONNECT BY PRIOR id = manager
  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)
  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)
  34. None
  35. • •

  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 ?
  37. SELECT start.name, SUM(e.time) AS distance, dst.name MATCH SHORTEST( (start) -[e]->*

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

    (dst) ) WHERE start.name = 'A' AND dst.name = 'E'
  40. None
  41. None
  42. None
  43. None
  44. None
  45. None
  46. • • • • •

  47. None
  48. None