Гость
Форумы
[новые:0]
/ Oracle
[новые:0]
[игнор отключен]
[закрыт для гостей]
/
не работают function-based index-ы
[новые:0]
5 сообщений из 5, страница 1 из 1
|
|
|
не работают function-based index-ы
|
|||
|---|---|---|---|
|
#18+
Господа ORACLE-гуру, объясните принцип работы function-based-index'ов: 1. Создаем тестовую таблицу и забываем ее: DROP TABLE TABLE_X1; CREATE TABLE TABLE_X1 ( ID NUMBER(10) primary key, STRING VARCHAR2(10) NOT NULL, Dummy1 NUMBER(10) ); DROP SEQUENCE TABLE_X1Sequence; CREATE SEQUENCE TABLE_X1Sequence START WITH 10 INCREMENT BY 1 NOMAXVALUE MINVALUE 1 NOCYCLE CACHE 5 ORDER; insert into TABLE_X1(ID, STRING, Dummy1) values (TABLE_X1Sequence.NEXTVAL, 'rollback', 10); insert into TABLE_X1(ID, STRING, Dummy1) values (TABLE_X1Sequence.NEXTVAL, 'commit', 10); insert into TABLE_X1(ID, STRING, Dummy1) values (TABLE_X1Sequence.NEXTVAL, 'alter', 10); insert into TABLE_X1(ID, STRING, Dummy1) values (TABLE_X1Sequence.NEXTVAL, 'user', 10); insert into TABLE_X1(ID, STRING, Dummy1) values (TABLE_X1Sequence.NEXTVAL, 'drop', 10); 2. Указываем оптимизатору использовать cost-based оптимизацию: ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS; 3. Создаем простой конкатенационный индекс: DROP INDEX TABLE_X1_Index1; CREATE INDEX TABLE_X1_Index1 ON TABLE_X1 (Dummy1 ASC, STRING ASC); 4. Смотрим план запроса: SELECT /*+ ALL_ROWS INDEX (TABLE_X1 TABLE_X1_Index1) */ COUNT(*) FROM TABLE_X1 WHERE Dummy1=10 AND STRING IS NOT NULL Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=20) 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'TABLE_X1_INDEX1' (NON-UNIQUE) (Cost=2 Card=1 Bytes=20) Читаем план и видим, что оптимизатор не обращался к таблице вообще (!!!), а использовал для COUNT таблицу индексов, которая скорее всего будет висеть в памяти и запрос обрабатывается мгновенно. Примечание: многие считают, что конструкция STRING IS NOT NULL ни коем образом не подцепляет индексы, однако, как видим, это не так. 5. Делаем function-based индекс: DROP INDEX TABLE_X1_Index1; CREATE INDEX TABLE_X1_Index1 ON TABLE_X1 (Dummy1 ASC, UPPER(STRING) ASC); 6. Делаем 2-запроса, чтобы убедиться, что как и в предыдущем случае оптимизатор будет использовать только индексы (первый запрос) и чтобы посмотреть выборку после запроса (второй): SELECT /*+ ALL_ROWS INDEX (TABLE_X1 TABLE_X1_Index1) */ COUNT(*) FROM TABLE_X1 WHERE Dummy1=10 AND UPPER(STRING) IS NOT NULL Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1 Card=1 Bytes=20) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_X1' (Cost=1 Card=1 Bytes=20) 3 2 INDEX (RANGE SCAN) OF 'TABLE_X1_INDEX1' (NON-UNIQUE) (Cost=2 Card=1) И к сожалению видим, что почему-то Оракл все-таки обратился к таблице: TABLE ACCESS - в 3-е строке Execution Plan. Что естественно ОЧЕНЬ ПЛОХО. Примечание: использование индексов на 2 поля продиктовано тем, что ПРОСТО НИКАК не удается подключить индекс типа CREATE INDEX TABLE_X1_Index1 ON TABLE_X1 (UPPER(STRING) ASC), всегда план говорит TABLE ACESS FULL. SELECT /*+ ALL_ROWS INDEX (TABLE_X1 TABLE_X1_Index1) */ * FROM TABLE_X1 WHERE Dummy1=10 AND UPPER(STRING) IS NOT NULL ID STRING DUMMY1 ---------- ---------- ---------- 12 alter 10 11 commit 10 14 drop 10 10 rollback 10 13 user 10 Видим, что выборка произошла строго по индексам. Если увеличить таблицу до большой величины порядка (100.000 строк), то выборка с использование индекса из П5 будет сравнительно страшно медленной по сравнению с П3. Возникает такое ощущение, что сканируется таблица индексов только для условия Dummy1=10. Далее с помощью полученных результатов (ROWID) идет обращение к таблице, проделывание над ней операций UPPER(STRING) и сортировка результатов по ASC. Напрашивается решение - просто отвести еще столбец UPPER_STRING, в котором при INSERT будет делаться UPPER_STRING=UPPER(STRING).Но это очевидная не экономия дискового пространства да и вообще полное извращение. У нас же в конце концов самая (imho) мощная СУБД. НУ ТОГДА ЖЕ КАКОЙ ВООБЩЕ СМЫСЛ ИСПОЛЬЗОВАНИЯ ИНДЕКСОВ НА ФУНКЦИИ. Видать я что-то не понимаю. Объясните как это работает. Заранее благодарен. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 14.11.2001, 18:29 |
|
||
|
не работают function-based index-ы
|
|||
|---|---|---|---|
|
#18+
Попробуй включить параметр QUERY_REWRITE_ENABLED=TRUE, если он FALSE, то funct-based index не подключаются (см. документ.) P.S. Мне что-то непонятно, в таблице STRING обьявлен как NOT NULL и зачем тогда в запросе использовать условие STRING IS NOT NULL? ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 15.11.2001, 09:15 |
|
||
|
не работают function-based index-ы
|
|||
|---|---|---|---|
|
#18+
1. QUERY_REWRITE_ENABLED - стоит 2. Объявление STRING NULL или NOT NULL - не принципиально Она необходима в строчке WHERE для того, чтобы в совокупности с хинтом оптимизатор точно выбрал нужный индекс. Например, если на таблицу создано несколько индексов, то даже если вы укажите хинт но не пропишите полное условие - то возможна ситуация либо FULL TABLE SCAN, либо будет использован вообще другой индекс. Или наоборот, указывая полную фразу WHERE, но не указывая хинт - будет полный бардак - подключиться индекс, который скорее всего даем меньшую стоимость, но не факт что тот, который мне необходим. Думаю, что сканирование происходит именно нужного индекса со 100% вероятностью тогда и только тогда, когда прописан хинт и используеться фраза WHERE, полностью описывающая индекс. Можно даже принять это за аксиому. Хотя, это мои досужие домыслы - на поверку в последней инстанции все может оказаться совсем не так. Эх, жаль нет выхода на тех-поддержку. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 15.11.2001, 09:49 |
|
||
|
не работают function-based index-ы
|
|||
|---|---|---|---|
|
#18+
Я не поленился и проделал следующие мероприятия: 1.create table a(id number primary key, nam varchar2(10) not null) 2.create index a_ind on a (id,upper(nam)) 3.insert into a select 14,'ttt' from dual ... 4.Запустил 2 запроса и посмотрел их план: select /*+ index(a a_ind) */ count(*) from a where id=10 and upper(nam) is not null select count(*) from a where id=10 and upper(nam) is not null Планы одинаковы: QUERY PLAN ----------------------------------------------------------------- ID PAR_ID COST BYTES PLAN ----------------------------------------------------------------- 0 1 20 SELECT STATEMENT Cost = 1 FIRST_ROWS 1 0 20 SORT AGGREGATE part[,] 2 1 2 20 INDEX RANGE SCAN A_IND part[,] ----------------------------------------------------------------- Как видишь все работает На всякий случай высылаю весь мой список параметров (их ненадо недооценивать иначе их бы не заводили) SQLWKS> select name||' : '||value from v$parameter 2> NAME||':'||VALUE -------------------------------------------------------------------------------- processes : 100 sessions : 115 timed_statistics : FALSE timed_os_statistics : 0 resource_limit : FALSE license_max_sessions : 0 license_sessions_warning : 0 cpu_count : 1 instance_groups : event : shared_pool_size : 20000000 shared_pool_reserved_size : 1000000 large_pool_size : 0 java_pool_size : 20000K java_soft_sessionspace_limit : 0 java_max_sessionspace_size : 0 pre_page_sga : FALSE shared_memory_address : 0 hi_shared_memory_address : 0 use_indirect_data_buffers : FALSE lock_sga : FALSE lock_name_space : enqueue_resources : 1285 nls_language : AMERICAN nls_territory : AMERICA nls_sort : nls_date_language : nls_date_format : nls_currency : nls_numeric_characters : nls_iso_currency : nls_calendar : nls_time_format : nls_timestamp_format : nls_time_tz_format : nls_timestamp_tz_format : nls_dual_currency : nls_comp : disk_asynch_io : TRUE tape_asynch_io : TRUE dbwr_io_slaves : 0 backup_tape_io_slaves : FALSE db_file_direct_io_count : 64 resource_manager_plan : lm_procs : 127 lm_ress : 6000 lm_locks : 12000 control_files : c:\database©ontrol01.ctl db_file_name_convert : log_file_name_convert : db_block_buffers : 7000 buffer_pool_keep : buffer_pool_recycle : db_block_checksum : FALSE db_block_size : 8192 db_block_lru_latches : 1 db_writer_processes : 1 db_block_max_dirty_target : 7000 max_commit_propagation_delay : 90000 compatible : 8.1.0 log_archive_start : TRUE log_archive_dest : c:\database\archive log_archive_duplex_dest : log_archive_dest_1 : log_archive_dest_2 : log_archive_dest_3 : log_archive_dest_4 : log_archive_dest_5 : log_archive_dest_state_1 : enable log_archive_dest_state_2 : enable log_archive_dest_state_3 : enable log_archive_dest_state_4 : enable log_archive_dest_state_5 : enable log_archive_max_processes : 1 log_archive_min_succeed_dest : 1 standby_archive_dest : %ORACLE_HOME%\RDBMS log_archive_format : ora%S.ARC log_buffer : 32768 log_checkpoint_interval : 8000 log_checkpoint_timeout : 0 db_files : 1020 db_file_multiblock_read_count : 2 read_only_open_delayed : FALSE parallel_server : FALSE parallel_server_instances : 1 gc_releasable_locks : 0 gc_rollback_locks : gc_files_to_locks : gc_defer_time : 10 thread : 0 fast_start_io_target : 7000 log_checkpoints_to_alert : FALSE recovery_parallelism : 0 control_file_record_keep_time : 7 dml_locks : 100 row_locking : always serializable : FALSE replication_dependency_tracking : TRUE instance_number : 0 transactions : 60 transactions_per_rollback_segment : 5 max_rollback_segments : 30 rollback_segments : transaction_auditing : TRUE fast_start_parallel_rollback : LOW db_block_checking : FALSE ent_domain_name : os_roles : FALSE rdbms_server_dn : max_enabled_roles : 20 remote_os_authent : FALSE remote_os_roles : FALSE O7_DICTIONARY_ACCESSIBILITY : TRUE remote_login_passwordfile : EXCLUSIVE dblink_encrypt_login : FALSE license_max_users : 0 db_domain : global_names : FALSE distributed_transactions : 15 commit_point_strength : 1 service_names : ora instance_name : mts_dispatchers : mts_servers : 0 mts_max_servers : 0 mts_max_dispatchers : 5 local_listener : mts_service : ora mts_listener_address : mts_multiple_listeners : FALSE open_links : 4 open_links_per_instance : 4 optimizer_features_enable : 8.1.5 fixed_date : audit_trail : NONE sort_area_size : 4000000 sort_area_retained_size : 2000000 sort_multiblock_read_count : 2 db_name : ora open_cursors : 50 ifile : sql_trace : FALSE os_authent_prefix : OPS$ optimizer_mode : FIRST_ROWS sql92_security : FALSE blank_trimming : FALSE always_anti_join : HASH partition_view_enabled : FALSE star_transformation_enabled : TRUE parallel_broadcast_enabled : TRUE parallel_adaptive_multi_user : TRUE parallel_threads_per_cpu : 2 parallel_automatic_tuning : FALSE always_semi_join : HASH optimizer_max_permutations : 80000 optimizer_index_cost_adj : 100 optimizer_index_caching : 0 query_rewrite_enabled : TRUE query_rewrite_integrity : enforced serial_reuse : DISABLE cursor_space_for_time : FALSE session_cached_cursors : 0 text_enable : FALSE remote_dependencies_mode : TIMESTAMP utl_file_dir : * plsql_v2_compatibility : FALSE plsql_load_without_compile : FALSE job_queue_processes : 2 job_queue_interval : 10 optimizer_percent_parallel : 0 optimizer_search_limit : 5 parallel_min_percent : 0 create_bitmap_area_size : 8388608 bitmap_merge_area_size : 1048576 parallel_min_servers : 6 parallel_max_servers : 16 parallel_instance_group : parallel_execution_message_size : 2148 hash_join_enabled : TRUE hash_area_size : 6000000 hash_multiblock_io_count : 2 background_dump_dest : %ORACLE_HOME%\RDBMS\TRACE user_dump_dest : %ORACLE_HOME%\RDBMS\TRACE max_dump_file_size : 10240 oracle_trace_enable : FALSE oracle_trace_facility_path : %ORACLE_HOME%\OTRACE\ADMIN\FDF\ oracle_trace_collection_path : %ORACLE_HOME%\OTRACE\ADMIN\CDF\ oracle_trace_facility_name : oracled oracle_trace_collection_name : oracle8 oracle_trace_collection_size : 5242880 object_cache_optimal_size : 102400 object_cache_max_size_percent : 10 session_max_open_files : 10 aq_tm_processes : 0 hs_autoregister : TRUE 195 rows selected. Удачи! ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 16.11.2001, 07:43 |
|
||
|
не работают function-based index-ы
|
|||
|---|---|---|---|
|
#18+
Спасибо, mmsmmsб действительно QUERY_REWRITE_ENABLED=TRUE - помогло. Печемуто в голове вертелась дурацкая мысль, что Session Privilegy QUERY_REWRITE дает мне чтото типа того при инициализации сессии: ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE Однако ничего подобного. Еще раз спасибо. Вам также удачи всегда и во всем. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 04.12.2001, 07:30 |
|
||
5 сообщений из 5, страница 1 из 1
|
|
Форумы
[новые:0]
/ Oracle
[новые:0]
[игнор отключен]
[закрыт для гостей]
/
не работают function-based index-ы
[новые:0]
