| Table Partitioning: [message #650984] |
Mon, 09 May 2016 07:05  |
 |
oracle12c
Messages: 1
Registered: May 2016
Location: Pakistan
|
Junior Member |
|
|
Hi,
I have created the following partitioned table and when I execute the below query on a Single partition as from "18 MAR 16" to "20 MAR 16", it goes on the full table scan, but when I exceeds the number of partition from one to two or more, than its work properly i.e. runs on specific partition.
--Query:
------------
SELECT ActivityLogsID, CreatedDate, LeaseID, LocalIP, LeaseType, Mac, PacketID
FROM ActivityLogs
WHERE LocalIP = '10.170.44.184'
AND Mac = '10:fe:ed:6a:5c:53'
AND CreatedDate BETWEEN TIMESTAMP' 2016-03-18 00:00:00' AND TIMESTAMP' 2016-03-20 23:59:59';
I have also specified the partition name in the FROM Clause:
FROM ActivityLogs PARTITION(ACTIVITYLOGS_MAR_2016), but its still goes on Full table scan.
Any suggestion please.
-- Table:
----------------
create table ACTIVITYLOGS
(
ACTIVITYLOGSID NUMBER not null,
CREATEDDATE TIMESTAMP(0),
LEASEID VARCHAR2(100),
LOCALIP VARCHAR2(100),
LEASETYPE VARCHAR2(100),
MAC VARCHAR2(100),
PACKETID VARCHAR2(100)
)
partition by range (CREATEDDATE)
(
partition ACTIVITYLOGS_DEC_2015 values less than (TIMESTAMP' 2016-01-01 00:00:00'),
partition ACTIVITYLOGS_JAN_2016 values less than (TIMESTAMP' 2016-02-01 00:00:00'),
partition ACTIVITYLOGS_FEB_2016 values less than (TIMESTAMP' 2016-03-01 00:00:00'),
partition ACTIVITYLOGS_MAR_2016 values less than (TIMESTAMP' 2016-04-01 00:00:00'),
partition ACTIVITYLOGS_APR_2016 values less than (TIMESTAMP' 2016-05-01 00:00:00'),
partition ACTIVITYLOGS_MAY_2016 values less than (TIMESTAMP' 2016-06-01 00:00:00'),
partition ACTIVITYLOGS_JUN_2016 values less than (TIMESTAMP' 2016-07-01 00:00:00'),
partition ACTIVITYLOGS_JUL_2016 values less than (TIMESTAMP' 2016-08-01 00:00:00'),
partition ACTIVITYLOGS_AUG_2016 values less than (TIMESTAMP' 2016-09-01 00:00:00'),
partition ACTIVITYLOGS_SEP_2016 values less than (TIMESTAMP' 2016-10-01 00:00:00'),
partition ACTIVITYLOGS_OCT_2016 values less than (TIMESTAMP' 2016-11-01 00:00:00'),
partition ACTIVITYLOGS_NOV_2016 values less than (TIMESTAMP' 2016-12-01 00:00:00'),
partition ACTIVITYLOGS_DEC_2016 values less than (TIMESTAMP' 2017-01-01 00:00:00');
-- Create/Recreate primary, unique and foreign key constraints
alter table ACTIVITYLOGS
add primary key (ACTIVITYLOGSID);
-- Create/Recreate indexes
create index IDX_ACTLOG_LOCALIP on ACTIVITYLOGS (LOCALIP)
tablespace ASA_INDEX;
create index IDX_ACTLOG_MAC on ACTIVITYLOGS (MAC)
tablespace ASA_INDEX;
create index INDX_ACTLOG_CREATDATE on ACTIVITYLOGS (CREATEDDATE);
Regards,
Zeeshan Khan.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
