DBA Blogs

FILE_ID vs RELATIVE_FNO

Tom Kyte - Sat, 2017-09-30 17:46
Hi TOM, Trying to understand the difference between FILE_ID & RELATIVE_FNO in dba_data_files and dba_extents.
Categories: DBA Blogs

Is UTL_MAIL supported in 11g EE

Tom Kyte - Sat, 2017-09-30 17:46
Hi Team, Wanted to know wherher utl_mail is supported in 11g EE.I installed the version in my local windows machine.I am able to connect to it via SQL Developer. I can see utl_smtp and utl_tcp packages are installed. I tried to install the utlmail...
Categories: DBA Blogs

What performs better NVL or DECODE for evaluating NULL values

Tom Kyte - Fri, 2017-09-29 23:26
Afternoon, Could anyone tell me which of the following statements would perform better? <code> SELECT 1 FROM DUAL WHERE NVL (NULL, '-1') = NVL (NULL, '-1') </code> OR <code> SELECT 1 FROM DUAL WHERE DECODE(NULL, NULL, '1', '0') = '...
Categories: DBA Blogs

Which Index is Better Global Or Local in Partitioned Table?

Tom Kyte - Fri, 2017-09-29 23:26
We have partitioned table based on date say startdate (Interval partition , For each day) We will use query that will generate report based on days (like report for previous 5 days) Also we use queries that will generate report based on hours (li...
Categories: DBA Blogs

SQL to find the ip address

Tom Kyte - Fri, 2017-09-29 23:26
Hi Tom, I want to capture the IP address of any client who has shutdown the db. Support currently in my db 5 clients are connected, one client shutdown the db, then I want to capture the IP address of client who has been shutdown the db. How to solv...
Categories: DBA Blogs

AWR

Tom Kyte - Fri, 2017-09-29 23:26
Hi Tom/Team, I am aware of the definition of terms used in AWR Report. but i want to know that - how to calculate and on what basis we need to calculate values listed for points in below 2 section of AWR report 1. Top 5 timed foreground event ...
Categories: DBA Blogs

BInary operator like AND, XOR

Tom Kyte - Fri, 2017-09-29 23:26
Hi i have a simple question can we use the binary operator like AND or XOR in a SQL statement. For example "select 1 AND 1 from dual;" result = 1 or true or "select 1 XOR 1 from dual;" if not, please can you tell me how can i do to have th...
Categories: DBA Blogs

Lots of archivelog generation when shrinking and compacting segments

Tom Kyte - Fri, 2017-09-29 23:26
Hi Tom, 1. what is the reason of huge redo and archivelog generation when compacting and shriking huge segments in 10g? 2. How it can be avoided or minimized? Thanks JP
Categories: DBA Blogs

Partitioned Indexes

Hemant K Chitale - Fri, 2017-09-29 10:15
Most discussions about Partitioning in Oracle are around Table Partitioning.  Rarely do we come across Index Partitioning.
A couple of days ago, there was an Oracle Community question on Partitioned Indexes.

So, here is a quick listing of Index Partitioning options  (these tests are in 11.2.0.4)


First, I start with a regular, non-partitioned table.

SQL> create table non_partitioned  
2 (id_col number,
3 data_col_1 number,
4 data_col_2 number,
5 data_col_3 varchar2(15)
6 )
7 /

Table created.

SQL>


I now attempt to create an Equi-Partitioned (LOCAL) Index on it.

SQL> create index equi_part on non_partitioned (id_col) local;
create index equi_part on non_partitioned (id_col) local
*
ERROR at line 1:
ORA-14016: underlying table of a LOCAL partitioned index must be partitioned


SQL>


As expected I can't create a LOCAL index on a non-partitioned table.

Can I create any partitioned index on this table ?

I try two different GLOBAL PARTITIONed Indexes

SQL> create index global_part   
2 on non_partitioned (id_col) global
3 partition by range (id_col)
4 (partition p_100 values less than (101),
5 partition p_200 values less than (201)
6 )
7 /
)
*
ERROR at line 6:
ORA-14021: MAXVALUE must be specified for all columns


SQL>
SQL> create index global_part
2 on non_partitioned (id_col) global
3 partition by range (id_col)
4 (partition p_100 values less than (101),
5 partition p_200 values less than (201),
6 partition p_max values less than (MAXVALUE)
7 )
8 /

Index created.

SQL>
SQL> create index global_part_comp
2 on non_partitioned (id_col, data_col_3) global
3 partition by range (id_col, data_col_3)
4 (partition p_1 values less than (101,'M'),
5 partition p_2 values less than (101,MAXVALUE),
6 partition p_3 values less than (201,'M'),
7 partition p_4 values less than (201,MAXVALUE),
8 partition p_max values less than (MAXVALUE, MAXVALUE)
9 )
10 /

Index created.

SQL>


So, I must have a MAXVALUE partition for the Index.  Note that the two indexes above are now Partitioned without the table itself being partitioned.

SQL> select index_name, partitioned
2 from user_indexes
3 where table_name = 'NON_PARTITIONED'
4 order by 1
5 /

INDEX_NAME PAR
------------------------------ ---
GLOBAL_PART YES
GLOBAL_PART_COMP YES

SQL>


The above indexes are Prefixed Global Partitioned Indexes. Can I create a Non-Prefixed Global Partitioned Index -- an Index where the Partition Key is not formed by the left-most columns of the index.

SQL> create index global_part_nonprefix
2 on non_partitioned (id_col, data_col_3) global
3 partition by range (data_col_1)
4 (partition p_1 values less than (101),
5 partition p_2 values less than (201),
6 partition p_max values less than (MAXVALUE)
7 )
8 /
partition by range (data_col_1)
*
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL>
SQL> !oerr ora 14038
14038, 00000, "GLOBAL partitioned index must be prefixed"
// *Cause: User attempted to create a GLOBAL non-prefixed partitioned index
// which is illegal
// *Action: If the user, indeed, desired to create a non-prefixed
// index, it must be created as LOCAL; otherwise, correct the list
// of key and/or partitioning columns to ensure that the index is
// prefixed

SQL>


So, I have proved that a Non-Partitioned Table cannot have a LOCAL Partitioned Index or a Non-Prefixed Global Partitioned Index but can still have a Global Partitioned Index where the Partition Key is left-prefixed from the Index Key. Also, that a Global Partitioned Index can be a Composite Index with columns of different datatypes.

Let me now proceed with a Partitioned Table.

SQL> create table partitioned
2 (id_col number,
3 data_col_1 number,
4 data_col_2 number,
5 data_col_3 varchar2(15)
6 )
7 partition by range (id_col)
8 (partition p_100 values less than (101),
9 partition p_200 values less than (201),
10 partition p_max values less than (MAXVALUE)
11 )
12 /

Table created.

SQL>


First, the Equi-Partitioned (LOCAL) Index.

SQL> create index part_equi_part
2 on partitioned (id_col) local
3 /

Index created.

SQL> select partition_name, partition_position
2 from user_ind_partitions
3 where index_name = 'PART_EQUI_PART'
4 order by 2
5 /

PARTITION_NAME PARTITION_POSITION
------------------------------ ------------------
P_100 1
P_200 2
P_MAX 3

SQL>


The usage of the LOCAL keyword instead of GLOBAL defines the Index as equi-partitioned with the table.  Index Partitions are automatically created to match the Table Partitions with the same Partition Names.  It is possible to create a LOCAL Partitioned Index and manually specify Partition Names but this, in my opinion, is a bad idea.  Attempting to manually name each Partition for the Index can result in a mis-match between Table Partition Names and Index Partition Names.

Next, I define two GLOBAL Partitioned Indexes on this table.

SQL> create index part_gbl_part  
2 on partitioned (data_col_1) global
3 partition by range (data_col_1)
4 (partition p_1 values less than (1001),
5 partition p_2 values less than (2001),
6 partition p_3 values less than (3001),
7 partition p_4 values less than (4001),
8 partition p_max values less than (MAXVALUE)
9 )
10 /

Index created.

SQL> create index part_gbl_part_comp
2 on partitioned (data_col_2, data_col_3) global
3 partition by range (data_col_2, data_col_3)
4 (partition p_a values less than (10, 'M'),
5 partition p_b values less than (10, MAXVALUE),
6 partition p_c values less than (20, 'M'),
7 partition p_d values less than (20, MAXVALUE),
8 partition p_e values less than (30, 'M'),
9 partition p_f values less than (30, MAXVALUE),
10 partition p_max values less than (MAXVALUE, MAXVALUE)
11 )
12 /

Index created.

SQL>
SQL> l
1 select index_name, partition_name, partition_position
2 from user_ind_partitions
3 where index_name in
4 (select index_name from user_indexes
5 where table_name = 'PARTITIONED'
6 )
7* order by 1,3
SQL> /

INDEX_NAME PARTITIO PARTITION_POSITION
------------------ -------- ------------------
PART_EQUI_PART P_100 1
PART_EQUI_PART P_200 2
PART_EQUI_PART P_MAX 3
PART_GBL_PART      P_1                       1
PART_GBL_PART P_2 2
PART_GBL_PART P_3 3
PART_GBL_PART P_4 4
PART_GBL_PART P_MAX 5
PART_GBL_PART_COMP P_A                       1
PART_GBL_PART_COMP P_B 2
PART_GBL_PART_COMP P_C 3
PART_GBL_PART_COMP P_D 4
PART_GBL_PART_COMP P_E 5
PART_GBL_PART_COMP P_F 6
PART_GBL_PART_COMP P_MAX 7

15 rows selected.

SQL>


The Equi-Partitioned (LOCAL) Index has the same number (and, recommended, names) of Partitions as the Table.
However, the GLOBAL Indexes can have different numbers of Partitions.

As with the first case, I cannot create a Global Non-Prefixed Partitioned Index (where the Index Partition key is not  a left-prefix of the Index).

SQL> create index part_global_part_nonprefix
2 on partitioned (id_col, data_col_3) global
3 partition by range (data_col_1)
4 (partition p_1 values less than (101),
5 partition p_2 values less than (201),
6 partition p_max values less than (MAXVALUE)
7 )
8 /
partition by range (data_col_1)
*
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL>


In this blog post, I haven't touched on Partial Indexing (a 12c feature).

I haven't touched on Unique LOCALly Partitioned Indexes.

I haven't demonstrated the impact of Partition Maintenance operations (TRUNCATE, DROP, MERGE, ADD, SPLIT) on LOCAL and GLOBAL Indexes here -- although I have touched on such operations and LOCAL indexes in earlier blog posts.
.
.
.

Categories: DBA Blogs

Calling Procedure Parallel

Tom Kyte - Fri, 2017-09-29 05:06
I have below procedure which in turn calls two other Procedures. It calls and works fine but the two procs runs serial. I want to run them parallel and get the results on the main procs cursor. How do I do that? I tried with dbms_job.submit but could...
Categories: DBA Blogs

Getting sub-string from two Clobs object and compare those substrings

Tom Kyte - Fri, 2017-09-29 05:06
Hi, I am new to CLOB objects but seems like I need to get my hands dirty on this. I have a CLOB column in my table and I need to get item SKU values from this column separated by commas. This is hoe my CLOB Column value looks like. ------- <...
Categories: DBA Blogs

External table concepts

Tom Kyte - Fri, 2017-09-29 05:06
Hi All, I am new to oracle external table concepts. Have a very basic query - if i have a csv with the below columns Col1, Col2, Col3 Col4 .... Coln and i want to insert only Col3 & Col4 into an oracle external table , what would be my ...
Categories: DBA Blogs

sql query to update a table based on data from other table

Tom Kyte - Fri, 2017-09-29 05:06
Hi, Looks like my other similar questions got closed, so asking a new question. I have a cust_bug_data table with 2 columns(ROOT_CAUSE, BUG_NUMBER) like as follows: <code>create table cust_bug_data(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER N...
Categories: DBA Blogs

Updating records with many-to-1 linked table relationship

Tom Kyte - Thu, 2017-09-28 10:46
I have an MS_ACCESS Query to convert to Oracle SQL. Access Query <code>UPDATE target_table T INNER JOIN source_table S ON T.linkcolumn = S.linkColumn SET T.field1 = S.field1, T.field2 = S.field2, T.field3 = S.field3;</code> Note: T...
Categories: DBA Blogs

Native dynamic sql - Refcursor

Tom Kyte - Thu, 2017-09-28 10:46
Tom, Here is an example...that i want to change one function to avoid redundant information. create or replace package p_ref_cursor is type ret_ref_cursor is ref cursor; end p_ref_cursor; / drop table "tab1"; create table "tab1" ...
Categories: DBA Blogs

ORA-06502 with CHAR parameter. What am I missing?

Tom Kyte - Thu, 2017-09-28 10:46
Sorry to bother you with a ORA-06502 error. But I'm not understanding this behavior. As I saw, the length fits (see the dbms_output in result showing that the length is 16). The only thing i can think is that in the procedure proc, pl/sql is...
Categories: DBA Blogs

ORA-00060 : Deadlock detected while waiting for resource in Multi-threaded Java Batch process

Tom Kyte - Wed, 2017-09-27 16:26
Hi Tom I have a multi-threaded batch process running on Production that fails due to "ORA-00060 : Deadlock detected while waiting for resource". I am getting following error message: <code>02:25:25,899 [CobolThread 34] ERROR Error executing upd...
Categories: DBA Blogs

Materialized Views: Refresh Statistics History - How can I see data in user_mvref_stmt_stats

Tom Kyte - Wed, 2017-09-27 16:26
Hi Masters, sorry for another question :-) and again a big, big thank You for Your hard work and the answers to our questions!! Could You please have a look into my test case? Some of the statement fail, because I do not have all the privs in l...
Categories: DBA Blogs

Using JSON_TABLE with aggregate functions

Tom Kyte - Wed, 2017-09-27 16:26
Hi Team DB Version: 12.2.0.0.3 On a high level, on successful completion of the scripts (from LiveSQL) you would have: 1) Loaded 10k users data in JSON format in STAGE_USER table. 2) Loaded 500 roles data in JSON format in STAGE_ROLE table. ...
Categories: DBA Blogs

ORA-00937 for aggregate in scalar subquery

Tom Kyte - Wed, 2017-09-27 16:26
<code>HI CAN ANY BODY EXPLAIN IN 1ST QUERY WHY IT IS ERROR BUT IN 2ND QUERY IT GIVES RESULT ? SQL> SELECT SUM(SAL), 2 ( 3 SELECT SUM(SAL) FROM EMP 4 ) 5 FROM EMP; SELECT SUM(SAL) FROM EMP * ERROR at line 3: ORA-00937: n...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs