Home » RDBMS Server » Performance Tuning » Partitioning huge table (10g)
Partitioning huge table [message #656512] Sun, 09 October 2016 13:14 Go to next message
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.


Re: Partitioning huge table [message #656513 is a reply to message #656512] Sun, 09 October 2016 13:27 Go to previous messageGo to next message
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?
Re: Partitioning huge table [message #656514 is a reply to message #656512] Sun, 09 October 2016 13:28 Go to previous messageGo to next message
John Watson
Messages: 8922
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.

Re: Partitioning huge table [message #656547 is a reply to message #656513] Tue, 11 October 2016 00:22 Go to previous messageGo to next message
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.


Re: Partitioning huge table [message #656549 is a reply to message #656547] Tue, 11 October 2016 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Add an index on JOB_NO.

Re: Partitioning huge table [message #656556 is a reply to message #656549] Tue, 11 October 2016 04:38 Go to previous messageGo to next message
gazzag
Messages: 1118
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?
Re: Partitioning huge table [message #656588 is a reply to message #656549] Wed, 12 October 2016 00:51 Go to previous messageGo to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
Thanks Micheal.
Re: Partitioning huge table [message #656589 is a reply to message #656556] Wed, 12 October 2016 00:52 Go to previous messageGo to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
yes gazzag , as per requirement we will be having job_number with varchar values.
Re: Partitioning huge table [message #656594 is a reply to message #656589] Wed, 12 October 2016 02:42 Go to previous message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
If JOB_NO is going to be numeric, it should be defined as such.
Previous Topic: compare two procedures for performance
Next Topic: Slow Query Sql
Goto Forum:
  


Current Time: Thu Mar 28 11:41:58 CDT 2024