Hinty (wskazówki) są (niestety) dość popularnym narzędziem w rękach deweloperów usiłujących nakłonić kosztowy optymalizator zapytań do zastosowania wybranego (przez dewelopera) planu wykonania zapytania. Wiele hintów wymaga podania nazwy obiektu zapytania (tabeli, indeksu, itp.), w stosunku do którego chcemy wymusić określone zachowanie optymalizatora. Co jednak zrobić w przypadku, gdy zapytanie operuje na perspektywie, a chcemy użyć hintu w stosunku do np. tabeli użytej wewnątrz definicji ten perspektywy (oczywiście zwykle nie mamy możliwości ingerowania w definicję perspektywy)? Z pomocą wtedy przychodzi dobrze udokumentowana możliwość zapisania w hincie referencji do bloku zapytania (Query Block). Oto bardzo prosty przykład.
Perspektywa ORDERS_VIEW jest oparta o tabelę ORDERS_TABLE. Tabela ORDERS_TABLE posiada indeks na kolumnie STATE. Zapytanie do perspektywy ORDERS_VIEW obecnie nie korzysta z indeksu. Celem naszej demonstracji będzie zmuszenie zapytania do użycia indeksu – poprzez użycie hintu poza definicją perspektywy (bezinwazyjnie).
create table orders_table (ordid number(6), state varchar2(20), amount number(8,2));
...insert...dbms_stats...
create index order_state_ind on orders_table(state);
create view orders_view as select * from orders_table;
select sum(amount) from orders_view where state='CLOSED';
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL|ORDERS_TABLE| 50 | 400 | 2 (0)|00:01 |
Aby możliwe było odwołanie się do tabeli ukrytej wewnątrz definicji perspektywy musimy znać nazwę tzw. bloku zapytania (Query Block), który reprezentuje definicję perspektywy. Można to osiągnąć na dwa sposoby – nadając tę nazwę jawnie za pomocą hintu QB_NAME() lub podglądając w planie wykonania zapytania nazwę wygenerowaną automatycznie przez optymalizator zapytań. Skorzystajmy z tego drugiego sposobu – zauważmy, że konieczne jest wyświetlenie planu wykonania w formie rozwiniętej („ALL”).
select sum(amount) from orders_view where state='CLOSED';
select * from table (dbms_xplan.display_cursor(null,null,'ALL'));
...
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL|ORDERS_TABLE| 50 | 400 | 2 (0)| 00:01|
...
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
2 - SEL$F5BB74E1 / ORDERS_TABLE@SEL$2
...
W sekcji „Query Block Name” widzimy nazwę bloku zapytania, w którym znajduje się referencja do tabeli ORDERS_TABLE – jest to „SEL$2”. Możemy teraz posłużyć się tą nazwą w treści hintu umieszczonego w naszym zapytaniu operującym na perspektywie. Istnieją dwie formy jej zapisu: nazwa bloku zapytania umieszczona jako pierwszy argument hintu poprzedzona symbolem „@” lub nazwa bloku zapytania dołączona do nazwy tabeli, również po znaku „@”.
select /*+ INDEX(@SEL$2 ORDERS_TABLE ORDER_STATE_IND)*/ sum(amount)
from orders_view where state='CLOSED';
select /*+ INDEX(ORDERS_TABLE@SEL$2 ORDER_STATE_IND)*/ sum(amount)
from orders_view where state='CLOSED';
select * from table (dbms_xplan.display_cursor(null,null,'ALL'));
...
|Id| Operation |Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | | 2 (100)|
| 1| SORT AGGREGATE | | 1 | 8 | |
| 2| TABLE ACCESS BY INDEX ROWID BATCHED |ORDERS_TABLE| 50 | 400 | 2 (0)|
|*3| INDEX RANGE SCAN |ORDER_STATE_IND|50| | 1 (0)|
W ten oto prosty sposób udaje się bezinwazyjnie nakłonić zapytanie w definicji perspektywy do użycia wskazanego indeksu.
Standardowa dokumentacja:
https://docs.oracle.com/database/121/SQLRF/sql_elements006.htm#SQLRF00219
https://docs.oracle.com/database/121/SQLRF/sql_elements006.htm#BABJIFFH