Hinty dla perspektyw

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

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *