Lekérdezések az Oracle demo tábláira vonatkozóan. A táblák nem mindegyike létezik a jelenlegi adatbázisunkban, a generált tervek csak például szolgálnak. Tábla elérése ROWID alapján --------------------------- SELECT e.employee_id, e.first_name, e.last_name FROM hr.employees e WHERE rowid='AAAVf1AAEAAADRiAAA'; TERV feltetel (access--filter) -------------------------------------------------- ------------------------------------------------- SELECT STATEMENT + + -- TABLE ACCESS + BY USER ROWID + EMPLOYEES -- Index csökkenő sorrendű elérése ------------------------------- SELECT * FROM hr.employees e WHERE department_id < 50 ORDER BY department_id DESC; TERV feltetel (access--filter) ----------------------------------------------------- ----------------------------------------- SELECT STATEMENT + + -- TABLE ACCESS + BY INDEX ROWID + EMPLOYEES -- INDEX + RANGE SCAN DESCENDING + EMP_DEPARTMENT_IX "DEPARTMENT_ID"<50 -- "DEPARTMENT_ID"<50 Index skip scan (az index felépítése: last_name, first_name) --------------- SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name FROM employees e WHERE first_name = 'Steven'; TERV feltetel (access--filter) -------------------------------------------------- ---------------------------------------------- SELECT STATEMENT + + -- INDEX + SKIP SCAN + EMP_NAME_IX "FIRST_NAME"='Steven' -- "FIRST_NAME"='Steven' Egy indexen belüli keresés -> INLIST, több eredmény uniója -> CONCATENATION --------------------------------------------------------------------------- SELECT /*+ USE_CONCAT */ * FROM hr.employees e WHERE manager_id = 108 OR department_id = 110 OR department_id=90; TERV -------------------------------------------------- SELECT STATEMENT + + CONCATENATION + + INLIST ITERATOR + + TABLE ACCESS + BY INDEX ROWID + EMPLOYEES INDEX + RANGE SCAN + EMP_DEPARTMENT_IX TABLE ACCESS + BY INDEX ROWID + EMPLOYEES INDEX + RANGE SCAN + EMP_MANAGER_IX JOIN műveletnél a szintaxis nem változtat a lényegen ---------------------------------------------------- SELECT c.cust_last_name, co.country_name FROM sh.customers c NATURAL JOIN sh.countries co; TERV feltetel (access--filter) -------------------------------------------------- ------------------------------------- SELECT STATEMENT + + -- HASH JOIN + + "C"."COUNTRY_ID"="CO"."COUNTRY_ID" -- TABLE ACCESS + FULL + COUNTRIES -- TABLE ACCESS + FULL + CUSTOMERS -- Nem equijoin ------------ SELECT s.amount_sold, p.promo_name FROM sh.sales s JOIN sh.promotions p ON (s.time_id BETWEEN p.promo_begin_date AND p.promo_end_date) ; TERV feltetel (access--filter) ----------------------------------------- ------------------------------------------------- SELECT STATEMENT + + -- MERGE JOIN + + -- SORT + JOIN + -- TABLE ACCESS + FULL + PROMOTIONS -- FILTER + + -- "S"."TIME_ID"<="P"."PROMO_END_DATE" SORT + JOIN + "S"."TIME_ID">="P"."PROMO_BEGIN_DATE" -- "S"."TIME_ID">="P"."PROMO_BEGIN_DATE PARTITION RANGE + ALL + -- TABLE ACCESS + FULL + SALES -- Külső join (outer) ---------- SELECT s.time_id, t.time_id FROM sh.sales s RIGHT OUTER JOIN sh.times t ON (s.time_id = t.time_id); TERV feltetel (access--filter) -------------------------------------------------- --------------------------------- SELECT STATEMENT + + -- HASH JOIN + OUTER + "S"."TIME_ID"(+)="T"."TIME_ID" -- TABLE ACCESS + FULL + TIMES -- PARTITION RANGE + ALL + -- TABLE ACCESS + FULL + SALES -- Ha vannak indexek, akkor persze azokat is tudja használni: TERV feltetel (access--filter) -------------------------------------------------- ---------------------------------- SELECT STATEMENT + + -- HASH JOIN + OUTER + "S"."TIME_ID"(+)="T"."TIME_ID" -- INDEX + FAST FULL SCAN + TIME_PK -- PARTITION RANGE + ALL + -- BITMAP CONVERSION + TO ROWIDS + -- BITMAP INDEX + FAST FULL SCAN + SALES_TIME_BIX -- Másik oldali külső join (right outer) ----------------------- SELECT s.time_id, t.time_id FROM sh.sales s LEFT OUTER JOIN sh.times t ON (s.time_id = t.time_id); TERV feltetel (access--filter) -------------------------------------------------- ---------------------------------- SELECT STATEMENT + + -- HASH JOIN + RIGHT OUTER + "S"."TIME_ID"="T"."TIME_ID"(+) -- TABLE ACCESS + FULL + TIMES -- PARTITION RANGE + ALL + -- TABLE ACCESS + FULL + SALES -- Teljes (kétoldali) külső join (az alsó ág keresi meg a Countries pár nélküli sorait, ----------------------------- ezért ennek a join-nak a végrehajtása sokkal költségesebb) SELECT c.cust_id, c.cust_last_name, co.country_name FROM sh.customers c FULL OUTER JOIN sh.countries co ON (c.country_id = co.country_id); TERV feltetel (access--filter) -------------------------------------------------- ---------------------------------------- SELECT STATEMENT + + -- VIEW + + -- UNION-ALL + + -- HASH JOIN + RIGHT OUTER + "C"."COUNTRY_ID"="CO"."COUNTRY_ID"(+) -- TABLE ACCESS + FULL + COUNTRIES -- TABLE ACCESS + FULL + CUSTOMERS -- HASH JOIN + ANTI + "C"."COUNTRY_ID"="CO"."COUNTRY_ID" -- TABLE ACCESS + FULL + COUNTRIES -- TABLE ACCESS + FULL + CUSTOMERS -- Nem korrelált alkérdés (az alkérdés eredményéből készít hash táblát) ---------------------- SELECT c.* FROM sh.customers c WHERE c.country_id IN (SELECT co.country_id FROM sh.countries co WHERE co.country_subregion = 'Asia'); TERV feltetel (access--filter) -------------------------------------------------- ------------------------------------- SELECT STATEMENT + + -- HASH JOIN + + "C"."COUNTRY_ID"="CO"."COUNTRY_ID" -- TABLE ACCESS + FULL + COUNTRIES -- "CO"."COUNTRY_SUBREGION"='Asia' TABLE ACCESS + FULL + CUSTOMERS -- Korrelált alkérdés ------------------ SELECT p1.* FROM sh.promotions p1 WHERE p1.promo_cost = (SELECT MAX(p2.promo_cost) FROM sh.promotions p2 WHERE p1.promo_category = p2.promo_category); TERV feltetel (access--filter) -------------------------------------------------- ------------------------------------------------- SELECT STATEMENT + + -- HASH JOIN + + "P1"."PROMO_COST"="VW_COL_1" AND "P1"."PROMO_CATEGORY"="PROMO_CATEGORY" VIEW + + VW_SQ_1 -- HASH + GROUP BY + -- TABLE ACCESS + FULL + PROMOTIONS -- TABLE ACCESS + FULL + PROMOTIONS -- Antijoin (NOT IN) (az alkérdésből lesz a vezető tábla, IN esetén lásd nem korrelált alkérdés) ----------------- SELECT c.* FROM sh.customers c WHERE c.cust_income_level = 'F: 110,000 - 129,999' AND c.country_id NOT IN (SELECT co.country_id FROM sh.countries co WHERE co.country_subregion = 'Europe'); TERV feltetel (access--filter) -------------------------------------------------- ------------------------------------------------- SELECT STATEMENT + + -- HASH JOIN + RIGHT ANTI + "C"."COUNTRY_ID"="CO"."COUNTRY_ID" -- TABLE ACCESS + FULL + COUNTRIES -- "CO"."COUNTRY_SUBREGION"='Europe' TABLE ACCESS + FULL + CUSTOMERS -- "C"."CUST_INCOME_LEVEL"='F: 110,000 - 129,999' Semijoin (EXISTS) (NOT EXISTS esetén Antijoin lesz, a meglévő index miatt használ NL-ot) ----------------- SELECT e.employee_id, e.first_name, e.last_name, e.salary FROM hr.employees e WHERE e.department_id = 80 AND e.job_id='SA_REP' AND EXISTS (SELECT 1 FROM oe.orders o WHERE e.employee_id = o.sales_rep_id); TERV feltetel (access--filter) -------------------------------------------------- ------------------------------------------------- SELECT STATEMENT + + -- NESTED LOOPS + SEMI + -- TABLE ACCESS + FULL + EMPLOYEES -- "E"."JOB_ID"='SA_REP' AND "E"."DEPARTMENT_ID"=80 INDEX + RANGE SCAN + ORD_SALES_REP_IX "E"."EMPLOYEE_ID"="O"."SALES_REP_ID" -- "O"."SALES_REP_ EXISTS nem korrelált (Az ilyen lekérdezésnek nem sok értelme van, nem is join-olja a táblákat) -------------------- SELECT e.employee_id, e.first_name, e.last_name, e.salary FROM hr.employees e WHERE e.department_id = 80 AND e.job_id='SA_REP' AND EXISTS (SELECT 1 FROM oe.orders); TERV feltetel (access--filter) -------------------------------------------------- ------------------------------------------------- SELECT STATEMENT + + -- FILTER + + -- EXISTS (SELECT /*+ */ 0 FROM "OE"."ORDERS" "ORDERS") TABLE ACCESS + FULL + EMPLOYEES -- "E"."JOB_ID"='SA_REP' AND "E"."DEPARTMENT_ID"=80 INDEX + FULL SCAN + ORDER_PK -- Felső N elemzés (Nem kell a teljes halmazt rendezni, elég az 5 legnagyobbat a memóriában tartani) --------------- SELECT * FROM (SELECT prod_id, prod_name, prod_list_price, prod_min_price FROM sh.products ORDER BY prod_list_price DESC) WHERE ROWNUM <= 5; TERV feltetel (access--filter) -------------------------------------------------- --------------------------- SELECT STATEMENT + + -- COUNT + STOPKEY + -- ROWNUM<=5 VIEW + + -- SORT + ORDER BY STOPKEY + -- ROWNUM<=5 TABLE ACCESS + FULL + PRODUCTS --