Избегайте полного сканирования таблицы - извлекать только первую строку
Я пытаюсь написать запрос, который извлекает только первую (случайную) строку, когда условие выполнено.
-- Create table
create table TRANSACTIONS_SAMPLE
(
institution_id NUMBER(5) not null,
id NUMBER(10) not null,
partitionkey NUMBER(10) default 0 not null,
cardid NUMBER(10),
accountid NUMBER(10),
batchid NUMBER(10) not null,
amt_bill NUMBER(16,3),
load_date DATE not null,
trxn_date DATE not null,
single_msg_flag NUMBER(5),
authaccounttype VARCHAR2(2 BYTE),
originator VARCHAR2(50),
amount NUMBER(16,3) default 0.000 not null,
embeddedfee NUMBER(16,3) default 0.000 not null,,
valuedate DATE,
startofinterest DATE,
minduevaluedate DATE,
postdate DATE,
posttimestamp DATE,
Status CHAR(4 BYTE) default 'NEW' not null,
)
partition by list (PARTITIONKEY)
(
partition 0002913151 values (1234567)
tablespace LIVE
pctfree 10
initrans 16
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
)
);
-- Create/Recreate indexes
create index TRANSACTIONS_SAMPLEI01 on TRANSACTIONS_SAMPLE (ACCOUNTID)
local;
create index TRANSACTIONS_SAMPLEI02 on TRANSACTIONS_SAMPLE (LOAD_DATE)
local;
create index TRANSACTIONS_SAMPLEI03 on TRANSACTIONS_SAMPLE (BATCHID)
local;
create index TRANSACTIONS_SAMPLEI04 on TRANSACTIONS_SAMPLE (POSTDATE)
local;
create index TRANSACTIONS_SAMPLEI05 on TRANSACTIONS_SAMPLE (POSTTIMESTAMP)
local;
create index TRANSACTIONS_SAMPLEI06 on TRANSACTIONS_SAMPLE (STATUS, PARTITIONKEY)
local;
create index TRANSACTIONS_SAMPLEI07 on TRANSACTIONS_SAMPLE (CARDID, TRXN_DATE)
local;
create unique index TRANSACTIONS_SAMPLEUI01 on TRANSACTIONS_SAMPLE (ID, PARTITIONKEY)
local;
-- Create/Recreate primary, unique and foreign key constraints
alter table TRANSACTIONS_SAMPLE
add constraint TRANSACTIONS_SAMPLEPK primary key (ID, PARTITIONKEY);
--QUERY
Select * From (
Select t.AccountId From Transactions_sample t Group by t.Accountid Having Count(t.AccountId) > 10 order by dbms_random.random)
Where Rownum = 1
Проблема с этим запросом - полное сканирование таблицы. Я хочу достичь тех же результатов без полного доступа к таблице. Есть идеи?
Спасибо
1 ответ
Вы можете получить его до полного сканирования индекса, используя TRANSACTIONS_SAMPLEI01, если вы добавите фильтр для where AccountId is not null, Но только если вы не хотите считать нулевые значения, конечно.
Столбец может иметь значение NULL, но индекс не содержит нулевых значений. Чтобы включить число нулей, он должен выполнить полное сканирование таблицы, потому что он не может получить это число из индекса. Если у вас есть этот фильтр, оптимизатор знает, что все значения идентификатора учетной записи должны быть в индексе, поэтому он должен ссылаться только на него, а не на саму таблицу.
explain plan for
Select * From (
Select t.AccountId From Transactions_sample t where AccountId is not null Group by t.Accountid Having Count(t.AccountId) > 10 order by dbms_random.random)
Where Rownum = 1;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 381125580
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 1 | 13 | 0 (0)| 00:00:01 | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 13 | 0 (0)| 00:00:01 | | |
|* 4 | FILTER | | | | | | | |
| 5 | SORT GROUP BY NOSORT | | 1 | 13 | 0 (0)| 00:00:01 | | |
| 6 | PARTITION LIST SINGLE| | 1 | 13 | 0 (0)| 00:00:01 | 1 | 1 |
|* 7 | INDEX FULL SCAN | TRANSACTIONS_SAMPLEI01 | 1 | 13 | 0 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
4 - filter(COUNT("T"."ACCOUNTID")>10)
7 - filter("ACCOUNTID" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
В качестве альтернативы, если столбец можно сделать не обнуляемым, тогда фильтр не потребуется.