OraFAQ Forum: Performance Tuning » Partitioning huge table

Home » RDBMS Server » Performance Tuning » Partitioning huge table (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Partitioning huge table [message #656512]

Sun, 09 October 2016 13:14

arifmd1705
Messages: 37
Registered: May 2016
Location: uae

Member

Hi experts,

I have a huge table containing data for around 10 years , like it is a transaction table, and data keeps on getting added.When i run the query or select it , if full table scan is performed , queries are becoming slow.Is there a method to partition the table and use only records of current year or data which is recent for transaction and querying while storing the data on some sort of partition like rarely used data, and in case if i require i can fetch it from there.

Report message to a moderator

Re: Partitioning huge table [message #656513 is a reply to message #656512]

Sun, 09 October 2016 13:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) ddl for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

In some cases a Full Table Scan (FTS) is the most efficient choice.
For query that does FTS, why can't you add index to allow it to perform better?

Report message to a moderator

Re: Partitioning huge table [message #656514 is a reply to message #656512]

Sun, 09 October 2016 13:28

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

This sounds like an ordinary tuning question. You need to to provide the ordinary information! The statement, the execution plan, the DDL of the objects, and so on.

Would partitioning help? I don't know. I do know that it is expensive. In my experience, partitioning is very much the last resort for SQL tuning. Specially in your old 10.something release. It is much better in 12.

Report message to a moderator

Re: Partitioning huge table [message #656547 is a reply to message #656513]

Tue, 11 October 2016 00:22

arifmd1705
Messages: 37
Registered: May 2016
Location: uae

Member

thanks blackswan and John, i am really sorry for not posting the DDL and explain plan, please find below the same.


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

SQL> desc cuting_pc_mc
 Name                            Null?    Type
 ------------------------------- -------- ----
 JOB_NO                                   VARCHAR2(12)
 STRUCT_NO                                VARCHAR2(12)
 BATCH_NO                                 VARCHAR2(12)
 PHASE_NO                                 VARCHAR2(12)
 MARK_NO                                  VARCHAR2(35)
 MARK_DESC                                VARCHAR2(2000)
 POS_NO                                   VARCHAR2(12)
 POS_DESC                                 VARCHAR2(2000)
 REV_NO                                   FLOAT(126)
 QTY                                      FLOAT(126)
 LENGTH                                   FLOAT(126)
 WIDTH                                    FLOAT(126)
 HEIGHT                                   FLOAT(126)
 WEIGHT                                   FLOAT(126)
 PART_TYPE                                VARCHAR2(240)
 PART_ID                                  VARCHAR2(240)
 BLANK                                    VARCHAR2(240)
 GRADE                                    VARCHAR2(240)
 OPER_CODE                                VARCHAR2(12)
 MACH_CODE                                VARCHAR2(12)
 STATUS                                   VARCHAR2(1)
 PCMC_SRNO                                NUMBER
 PCMC_HOLD_YN_NUM                         VARCHAR2(1)
 PCMC_REV_CODE                            VARCHAR2(20)
 PCMC_REV_REMARKS                         VARCHAR2(2000)
 HOLE_DIA1_TOP_FLG_QTY                    VARCHAR2(20)
 HOLE_DIA1_BOT_FLG_QTY                    VARCHAR2(20)
 HOLE_DIA2                                VARCHAR2(20)
 HOLE_DIA2_WEB_QTY                        VARCHAR2(20)
 HOLE_DIA2_TOP_FLG_QTY                    VARCHAR2(20)
 FLANGE_THICKNESS                         NUMBER
 WEB_THICKNESS                            NUMBER
 HOLE_DIA1_TOTAL                          NUMBER
 HOLE_DIA2_TOTAL                          NUMBER
 HOLE_DIA3_TOTAL                          NUMBER
 HOLE_DIA4_TOTAL                          NUMBER
 HOLE_DIA5_TOTAL                          NUMBER
 STAGE                                    NUMBER
 HOLE_DIA1                                VARCHAR2(20)
 HOLE_DIA1_WEB_QTY                        VARCHAR2(20)
 HOLE_DIA5                                VARCHAR2(20)
 HOLE_DIA5_WEB_QTY                        VARCHAR2(20)
 HOLE_DIA5_TOP_FLG_QTY                    VARCHAR2(20)
 HOLE_DIA5_BOT_FLG_QTY                    VARCHAR2(20)
 HOLE_DIA3_BOT_FLG_QTY                    VARCHAR2(20)
 HOLE_DIA4                                VARCHAR2(20)
 HOLE_DIA4_WEB_QTY                        VARCHAR2(20)
 HOLE_DIA4_TOP_FLG_QTY                    VARCHAR2(20)
 HOLE_DIA4_BOT_FLG_QTY                    VARCHAR2(20)
 HOLE_DIA2_BOT_FLG_QTY                    VARCHAR2(20)
 HOLE_DIA3                                VARCHAR2(20)
 HOLE_DIA3_WEB_QTY                        VARCHAR2(20)
 HOLE_DIA3_TOP_FLG_QTY                    VARCHAR2(20)


CREATE INDEX CUTING_PC_MC_002 ON CUTING_PC_MC
(POS_NO)
LOGGING
TABLESPACE ORIONINDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          61M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX CUTING_PC_MC_001 ON CUTING_PC_MC
(MARK_NO)
LOGGING
TABLESPACE ORIONINDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          104M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;



SQL> EXPLAIN PLAN FOR
  2  SELECT MARK_NO,POS_NO,OPER_CODE FROM
  3  CUTING_PC_MC
  4  WHERE job_no='1610';

Explained.

SQL>  select plan_table_output
  2      from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2625378981

-------------------------------------------------------
| Id  | Operation         | Name         | Cost (%CPU)|
-------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 21298   (1)|
|*  1 |  TABLE ACCESS FULL| CUTING_PC_MC | 21298   (1)|
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("JOB_NO"='1610')

13 rows selected.

Report message to a moderator

Re: Partitioning huge table [message #656549 is a reply to message #656547]

Tue, 11 October 2016 00:34

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Add an index on JOB_NO.

Report message to a moderator

Re: Partitioning huge table [message #656556 is a reply to message #656549]

Tue, 11 October 2016 04:38

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Also, why is JOB_NO (and a few others by the look of it) VARCHAR2 and not NUMBER?

Report message to a moderator

Re: Partitioning huge table [message #656588 is a reply to message #656549]

Wed, 12 October 2016 00:51

arifmd1705
Messages: 37
Registered: May 2016
Location: uae

Member

Thanks Micheal.

Report message to a moderator

Re: Partitioning huge table [message #656589 is a reply to message #656556]

Wed, 12 October 2016 00:52

arifmd1705
Messages: 37
Registered: May 2016
Location: uae

Member

yes gazzag , as per requirement we will be having job_number with varchar values.

Report message to a moderator

Re: Partitioning huge table [message #656594 is a reply to message #656589]

Wed, 12 October 2016 02:42

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

If JOB_NO is going to be numeric, it should be defined as such.

Report message to a moderator

Previous Topic:	compare two procedures for performance
Next Topic:	Slow Query Sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 00:48:30 CST 2025