PL/SQL :: Index Not Being Used In Group By

Sep 14, 2012

Here is the scenario with examples. Big table 333 to 500 million rows in the table. Statistics are gathered. Histograms are there. Index is not being used though. Why?

  CREATE TABLE "XXFOCUS"."some_huge_data_table"
   (  "ORG_ID" NUMBER NOT NULL ENABLE,
  "PARTNERID" VARCHAR2(30) NOT NULL ENABLE,
  "EDI_END_DATE" DATE NOT NULL ENABLE,
  "CUSTOMER_ITEM_NUMBER" VARCHAR2(50) NOT NULL ENABLE,
  "STORE_NUMBER" VARCHAR2(10) NOT NULL ENABLE,
  "EDI_START_DATE" DATE,

[Code]...

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT num_rows FROM user_tables s WHERE s.table_name = 'some_huge_data_table';

  NUM_ROWS                                                                     
----------                                                                     
333338434                                                                     

SQL> SELECT MAX(edi_end_date)
  2    FROM some_huge_data_table p
  3   WHERE p.org_id = some_number
  4     AND p.partnerid = 'some_string';

MAX(EDI_E                                                                      
---------                                                                      
13-MAY-12                                                                      

Elapsed: 00:00:00.00

SQL> explain plan for

  2  SELECT MAX(edi_end_date)
  3    FROM some_huge_data_table p
  4   WHERE p.org_id = some_number
  5     AND p.partnerid = 'some_string';

Explained.

SQL> /

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------
Plan hash value: 2104157595                                                                                                                                                                           
--------------------------------------------------------------------------------------------       
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |       
--------------------------------------------------------------------------------------------       
|   0 | SELECT STATEMENT             |             |     1 |    22 |     4   (0)| 00:00:01 |       
|   1 |  SORT AGGREGATE              |             |     1 |    22 |            |          |       
|   2 |   FIRST ROW                  |             |     1 |    22 |     4   (0)| 00:00:01 |       
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| some_huge_data_table_PK |     1 |    22 |     4   (0)| 00:00:01 |       
--------------------------------------------------------------------------------------------       

SQL> explain plan for

  2  SELECT MAX(edi_end_date),
  3         org_id,
  4         partnerid
  5    FROM some_huge_data_table
  6   GROUP BY org_id,
  7            partnerid;

Explained.

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------
Plan hash value: 3950336305                                                                                                                                                                            
-------------------------------------------------------------------------------                    
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                    
-------------------------------------------------------------------------------                    
|   0 | SELECT STATEMENT   |          |     2 |    44 |  1605K  (1)| 05:21:03 |                    
|   1 |  HASH GROUP BY     |          |     2 |    44 |  1605K  (1)| 05:21:03 |                    
|   2 |   TABLE ACCESS FULL| some_huge_data_table |   333M|  6993M|  1592K  (1)| 05:18:33 |                    
-------------------------------------------------------------------------------      

Why wouldn't it use the index in the group by? If I write a loop to query for different partnerid (there are only three), the whole things takes less than a second.

btw, I gave the index hint too. Didn't work. Version mentioned in the example.

View 18 Replies


ADVERTISEMENT

SQL & PL/SQL :: Index And Group By

Sep 5, 2012

I have a query that extracts the row with a max(record_date) within a group.

select ssn, fund, type_indicator, annuitant, cur_year, record_date, guarantee
from LC_MORTALITY
where (ssn, fund, type_indicator, annuitant, cur_year, record_date) in
(select ssn, fund, type_indicator, annuitant, cur_year, max(record_date)
from LC_MORTALITY
group by ssn, fund, type_indicator, annuitant, cur_year);

the table has index that matches the group by clause exactly.

create index IDX_LC_MORTALITY_sftayd on LC_MORTALITY
(SSN,
FUND,
TYPE_INDICATOR,
ANNUITANT,
cur_year,
record_date

However, the plan ignores the index

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1963K| 241M| | 1375K (4)| 05:21:04 |
|* 1 | HASH JOIN RIGHT SEMI| | 1963K| 241M| 9701M| 1375K (4)| 05:21:04 |
| 2 | VIEW | VW_NSO_1 | 145M| 8038M| | 241K (12)| 00:56:22 |
| 3 | HASH GROUP BY | | 145M| 8038M| | 241K (12)| 00:56:22 |
| 4 | TABLE ACCESS FULL| LC_MORTALITY | 145M| 8038M| | 219K (3)| 00:51:13 |
| 5 | TABLE ACCESS FULL | LC_MORTALITY | 145M| 9840M| | 222K (5)| 00:51:49 |
---------------------------------------------------------------------------------------------
[code]...

It uses the index but still shows "table access full" in one place. Why is there still a full access?Can I do anything to optimize further?

View 9 Replies View Related

PL/SQL :: To Create Function Based Index For Group Function Columns

Jun 15, 2012

Is anyway to create function based index for group function columns.

For example

select max(timestamp),min(age),averge(sal).... ... .. from tab;

View 5 Replies View Related

Group Records With Less Than One Hour Separation And Count How Many Per Group

Nov 1, 2013

I'm trying to group sets of data based on time separations between records and then count how many records are in each group.

In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5

SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';

[code]....

Group 1 = 8:00:22 to 8:41:22

Group 2 = 11:35:47 to 11:35:47

Group 3 = 15:13:46 to 15:13:46

Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.

View 4 Replies View Related

SQL & PL/SQL :: Combining Rownum And Group By Gives - Not A Group By Expression

Jun 23, 2011

I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :

select ROWNUM,department_id,sum(salary) from employees group by department_id

If i remove rownum, it gives the correct output. Why can't rownum be used here ?

View 16 Replies View Related

SQL & PL/SQL :: Group Through Expression Inside Group By?

May 17, 2011

Refer to the txt file to create table and insert data.

I executed the following query-

SELECT priority, detail, COUNT(1) FROM TEST GROUP BY priority, detail

and got the following result-

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial TSS 1
StandardInitial development 10
StandardProduction deployment5
High PriorPatch 1

Now I want that Initial TSS and Initial development should be combined as Initial together and I should get the result as follows:

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial 11
StandardProduction deployment5
High PriorPatch 1

View 3 Replies View Related

Text :: Index For Domain Index With Composite Domain Index (CDI) Very Slow

Jun 27, 2012

I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:

Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....

I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.

View 5 Replies View Related

Performance Tuning :: Local Index Versus Global Index On Partitioned Table

Jun 28, 2011

I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.

View 6 Replies View Related

Server Administration :: Convert Global Index To Local Index

Jun 23, 2011

I have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.

I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.

View 5 Replies View Related

SQL & PL/SQL :: ORA-01502 - Index Or Partition Of Such Index Is In Unusable State?

Nov 29, 2010

I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.

View 3 Replies View Related

Performance Tuning :: Index With NVL / Query Is No Longer Using Index

Nov 19, 2010

I have a query which had a join:

a.c1=b.c1 and a.c2=@var

where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like

a.c1(+)=b.c1 and nvl(a.c2,@var)=@var

This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')

But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?

View 2 Replies View Related

Performance Tuning :: Force Index If Table Not Using Index?

Aug 9, 2013

How to force an index if the table not using the index?

View 10 Replies View Related

When Index Lock Contention Occurs / How To Know Which Index Is Causing

Aug 27, 2012

We have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.

This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.

Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.

View 5 Replies View Related

SQL & PL/SQL :: Create Non-cluster Index On A Clustered Index?

Jul 11, 2012

Can we create non-cluster index on a clustered index?

View 5 Replies View Related

SQL & PL/SQL :: Difference Between Index And Clustered Index

Aug 3, 2010

Any on give explanation for difference between Index and Clustered Index?

It will be great if i get explanation how memory allocation and Execution takes place.?

View 4 Replies View Related

Performance Tuning :: Difference Between Index Rebuild And Index Rebuild Online?

Jan 10, 2011

What is the difference between index rebuild and index rebuild online.

View 3 Replies View Related

To Add One Disk Group Space To Another Disk Group

May 9, 2011

Is there any way to add one disk group space to another disk group. Because One of my disk group is full i want to add space in to that group.

View 1 Replies View Related

SQL & PL/SQL :: Get Count On Group And Total Count For Each Group

Mar 23, 2013

I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )

select
c.Event,
c.code,
count(1) as calls,
total.total_count,
count(1) / total.total_count * 100 as pct_of_total
from
table1 c

[Code]....

[Edit MC: add code tags, do it yourself next time]

View 4 Replies View Related

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

Oct 5, 2013

Let's consider such table that all rows fit into single block:

SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin

[code].....

why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2

View 8 Replies View Related

SQL & PL/SQL :: GROUP BY

Oct 10, 2011

CREATE TABLE TEMP_GP_SEQ
(
COL1 NUMBER,
COL2 NUMBER,
COL3 NUMBER,
COL4 NUMBER,
COL5 NUMBER,
COL6 NUMBER,
COL7 CHAR (25)
);

[Code]...

I need to update the TEMP_GP_SEQ table and on the COLUMN COL7 based on the grouping range mentioned above using the sequence created. But the sequence should not be incremented for each and every record, it should be incremented only for change of groups. For example

SELECT *
FROM TEMP_GP_SEQ;

COL1 COL2 COL3 COL4 COL5 COL6 COL7
110100
110101
12101001

[Code]...

After update it should look like

SELECT *
FROM TEMP_GP_SEQ;

COL1 COL2 COL3 COL4 COL5 COL6 COL7
110100
110101
12101001M2

[Code]...

Actually the group by fetched two rows with a total record count of 4 (2 records in both the rows). SO the COL7 is updated for these four rows with M as a prefixed word and the number followed after M is from the sequence. Here the number is changing (sequence is incrementing) only when there is a change in grouping criteria and it remains the same throughout a group.

View 19 Replies View Related

Using GROUP BY To Get Counts

Jan 22, 2008

how to logically connect two tables. Here is an example of what I'm trying to do:

HOSTS TABLE CPU TABLE
----------- ---------
ID HOST ID CPU
01 host1 01 proc01
02 host2 01 proc02
03 host3 02 proc01
02 proc02
02 proc03
02 proc04
03 proc01

Based on the above, I can see that 'host1' has 2 CPUs, 'host2' has 4 CPUs, and 'host3' has 1 CPU. What I'd like to do is create a query that would output:

HOST CPU
----------
host1 2
host2 4
host3 1

I'm looping through the hosts and passing the them into another query as bind variables. That's slow and cannot be the best way to do this. I'm aware that I need to using a GROUP BY HAVING COUNT, but that doesn't seem to be working. It tends to return the total count of all CPUs rather than CPUs per host.

View 2 Replies View Related

Group Policy In VPD

Jun 19, 2013

Give me some examples of creating grouped in VPD .

Group policy example for
1) Grouped policy on same table for different columns
2) Grouped policy on two different table for different columns

View 1 Replies View Related

SQL & PL/SQL :: How To Group By Data

Sep 8, 2011

My requirement is Data from a TableA has to be provided as an overall view

TABLEA
ID ENTITY REQ_FLG PAR_FLG EXT_FLG
CONV_1 ACCNT Y Y Y
CONV_1 PROD Y Y N
CONV_1 ADDR Y N N
CONV_2 DID Y N N
CONV_2 ORDER Y N N

Required to show the data in report as

ID Expand View_Report Populate ENTITY QRY_STATUS
CONV_1 Expand Report Populate
ACCNT Y Y Y
PROD Y Y Y
ADDR Y N N
CONV_2 Expand Report Populate
DID Y N N
ORDER Y N N

Where "Expand", "Report", "Populate" are provided as Hard coded values in query.

Sample Query.
SELECT ID
,'Expand' AS EXPAND
,'Report' AS VIEW_REPORT
, 'Populate / Reset' AS POP
, DECODE(MN_TBL.ENTITY,NULL,NULL,ENTITY) AS ENTITY
, REQ_FLG || ' ' || PAR_FLG || ' ' || EXT_FLG AS QRY_STATUS
FROM TABLEA
GROUP BY GROUPING SETS
((ID), (ENTITY, REQ_FLG , PAR_FLG , EXT_FLG ))
ORDER BY CONVERSION_ID, ENTITY

Above query works fine, where single ID is present

ID Expand View_Report Populate ENTITY QRY_STATUS
CONV_1 Expand Report Populate
ACCNT Y Y Y
PROD Y Y Y
ADDR Y N N

But when more than one ID is present the entire thing collapses

View 5 Replies View Related

SQL & PL/SQL :: Group By Thursday

Jun 26, 2013

My weekly reports should start from Thursday and it ends on Wednesday . I need to aggregate data from Thursday to Wednesday. At the end of every month i generate report for the last 4 weeks.

I couldn't find a way to group by correctly. When I make group by trunc(fragment_date,'day')+2 and check the data it aggregates data from Monday to Sunday.

View 7 Replies View Related

SQL & PL/SQL :: Group By Statement

Oct 27, 2011

SELECT COUNT(c.country_id)
FROM countries c,
employees e,
locations l,
departments d

[Code]....

I really dont know when and where to use the group by statement from the query above when I added C.country name before the word from I'm always getting this error "ORA-00979: not a GROUP BY expression"

View 12 Replies View Related

SQL & PL/SQL :: Group By Clause

Mar 31, 2011

This time, I am going to provide the DML statements.

I have a simple table with 3 fields in it.I want to group by ACCT_NUMBER and sum of BALANCE field. But I want to get the description of the first row in a group.

the statements below. Here there are two groups of records 2001 and 2002. My sql(which I am working on) should return the following :

2001 EMPL TRAINING-MIS 20
2002 OTHER PROF SERV-HR 40

The following query will group by ACCT_NUMBER and sum of the BALANCE field. But how can I get the DESCRIPTION?

SELECT ACCT_NUMBER, SUM(BALANCE)
FROM TEST
GROUP BY ACCT_NUMBER
CREATE TABLE "TEST"
("ACCT_NUMBER" VARCHAR2(20 BYTE),
"DESCRIPTION" VARCHAR2(20 BYTE),

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Selecting 1 Row From A Group?

Jul 3, 2011

I am having trouble selecting the row that has max(pay_period) of 13. Here is some test data.

CREATE TABLE TESTME
(
SSN VARCHAR2(11 BYTE),
PAY_PERIOD VARCHAR2(3 BYTE),
PAY_YEAR NUMBER,
KRONOS_ID VARCHAR2(6 BYTE),
LAST_NAME VARCHAR2(15 BYTE),
FIRST_NAME VARCHAR2(14 BYTE),
ADJ_SALARY NUMBER
)

[code]....

View 17 Replies View Related

SQL & PL/SQL :: Group By For Getting Count

Mar 7, 2013

I need suing group by for getting count.I have a table with columns below

SYS_AUDIT_IDSYS_AUDIT_PROG_IDPROG_FINDING_ID_COUNT

178921652
178921641
178921631
179321521
179321511
179321501
179321491
179321461

I want to count number of SYS_AUDIT_PROG_ID for each audit and count of PROG_FINDING_ID_COUNT

I want to get
1789 3 4

I tried this query but this is not working

[code]select sys_audit_id ,count (sys_audit_prog_id), count(prog_finding_id_count) from

my_table sub
group by sys_audit_id [/code]

View 2 Replies View Related

SQL & PL/SQL :: Other Columns When Using Group By

Feb 18, 2011

I have a table OS_CURRENTSTEP , and OS_WFENTRY

CREATE TABLE OS_CURRENTSTEP
(
ID NUMBER,
ENTRY_ID NUMBER NOT NULL,
STEP_ID INTEGER NOT NULL,
ACTION_ID INTEGER,
OWNER VARCHAR2(20 BYTE),
START_DATE DATE,

[Code]...

I need count of step_id from os_currentstep wh

here is the query

select count(step_id), step_id from os_currentstep where owner='Marty' group by step_id

this gives me the count I also need the name associated with this step_id from table OS_WFENTRY

, I cannot query name from step_id ,

View 5 Replies View Related

SQL & PL/SQL :: Group Functions

Jul 2, 2010

count the no: of emp working under each manager? and instead of manager number display the manager name

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved