Feed aggregator

PL/SQL Performance Tuning

AskTom - Hot Questions - Thu, 04/12/2018 - 00:00
My stored PROCEDURE searches for potential duplicate PeopleSoft Vouchers (PS_VOUCHER, PS_VOUCHER_LINE, PS_DISTRIB_LINE, PS_VENDOR, PS_PYMNT_VCHR_XREF). The first step is to get the set of unique concatenated/delimited values for records that are cur...

Move subpartition to another partition

AskTom - Hot Questions - Thu, 04/12/2018 - 00:00
Hi. Today I've discovered another issue on the DB I've inherited. I've found this range subpartitions in wrong partitions. Let me try to explain. The scenario is: Partitions with month values subpartitioned by weeks, so I expect to have a ...

Is safe to use row_number() over (order by null) as a rownum alternative?

AskTom - Hot Questions - Fri, 03/23/2018 - 00:00
Hi, I did some testing and it seems that row_number() over (order by null) could be used as an alternative to oracle's rownum pseudocolumn. Is this behavior by design or is just a coincidence? I'm trying this because some queries are very slow...

How to Optimize design a 1000+ columns in a Oracle DB

AskTom - Hot Questions - Tue, 03/20/2018 - 00:00
Hi, We need to store 5 minute interval data, with additional attributes, which will be around 1000+ columns. How to design this optimally for a Fact Table? Doe we have any columnar design in Oracle, such as a vertical split? Please provide your ex...

using max function

AskTom - Hot Questions - Sun, 03/18/2018 - 00:00
Hi, Look at here..... Find highest salary in each department without using MAX function. Note:- Use a single SELECT statement only. For an added complexity (optional): try not using ANY functions a...

Datapump SQL minitoring with SQL (not PL/SQL)

AskTom - Hot Questions - Thu, 03/01/2018 - 00:00
I'm on 11.2 and 12.1 and would like to monitor datapump sessions with pure SQL (I am not allowed to create objects on the databases). I can monitor using: <code> select ss.sid , s.job_name , j.state --, j.job_mode --, j.degree , dbms_rcvm...

Foreign key constraint DDL & locks

AskTom - Hot Questions - Wed, 02/28/2018 - 00:00
For the period when DDL runs to add FK constraint (on un-indexedcolumn), both child and parent tables are unavailable to be read by 2nd session. Is this behaviour expected / documented? Is there anyway to avoid issue (which manifests in applicati...

database performance select count

AskTom - Hot Questions - Fri, 02/23/2018 - 00:00
Hi Team , i have query mentioned below : T1(id number , name varchar2 (20)); Column ID is full of null in table T1. select count(*) from table t1 where id is not null and name like :b1; This query is going on FTS , so we rewritten i...

Checking the checksum during purging process

AskTom - Hot Questions - Thu, 02/22/2018 - 00:00
Hi, We are developing an Archiving mechanism in which there is a procedure defined in the package that achieves the old partitions(according some business logic). We don't purge the partition as soon as it gets archived. We truncate the partition af...

Using SELECT * combined with WITH-CLAUSE - Bad Practice? View gets compiled with static columns list

AskTom - Hot Questions - Tue, 02/20/2018 - 00:00
Hey guys, I have a question regarding clean SQL Code / Bad Practice around the use of wildcards in SELECT-Statements. In the provided example I have a base-query with a huge list of columns selected and two (or more) sources I need to have combin...

ORA-01502 with hardcoded index hints

AskTom - Hot Questions - Mon, 02/19/2018 - 00:00
We have EBS 12.1.3, which has several indexes, that we disable for bulk loading at night and rebuild them once done. While they are disabled some queries get ORA-01502 because of hard coded index hint on these unusable indexes, although we have skip...

Update current row witrh values from previous row

AskTom - Hot Questions - Thu, 02/15/2018 - 00:00
Hi, I'm searching for a solution to solve update with a single SQL statement instead of a PL/SQL procedure: <code>create table test (stock_date DATE, stock NUMBER(5), stock_in NUMBER(5), stock_out NUMBER(5), stock_val NUMBER(5)); INSERT INTO tes...

SUBPARTITION ELIMINATION

AskTom - Hot Questions - Wed, 02/14/2018 - 00:00
Table with 10 years worth of data Has two key date columns. Queries are always by one of the dates. <b>But never both</b>. So how to get partition elimination to work for both when really can only partition by 1 column. **Thought was - since ...

How to Improve the Performance of Group By with Having

AskTom - Hot Questions - Wed, 02/14/2018 - 00:00
I have a table t containing three fields accountno, tran_date, amount and amount is always > 0. . There are many records for each accountno. I want to select all the accountnos where the sum(amount) > 100. The simple query is like this <code>...

Select data without duplicate

AskTom - Hot Questions - Wed, 02/14/2018 - 00:00
Right now data like this, Case# Unit# ------------------------ X0002270301 null X0002460601 T0707802116 X0002460601 null X0002683101 null x0002683101 T0721128616 x0002922501 null x0005125201 null x0005125201 T0813774816 ...

Using defined variable (ampersand) for a part of column name

AskTom - Hot Questions - Sun, 02/11/2018 - 00:00
Good day. I am having problem to define <b><u>only part</u></b> of a column name as a variable (using ampersand). Script example: <b><i>Select &Product._ID, &Product._NAME, &Product._SALES, &Product._DEBT From TABL...

Using SYS_GUID() has primary key in large table

AskTom - Hot Questions - Thu, 02/08/2018 - 00:00
Hey, I have a large table 600 million rows, approximately 70gb, not partitioned (yet), a few indices, etc. I have reports that query this table often but they're very slow (20-60 minutes at times). I want to create this materialized view but ...

Materialized views and Synonyms

AskTom - Hot Questions - Mon, 02/05/2018 - 00:00
Good day. I need help with refresh materialized view. I created synonym MySynonym: <code>CREATE OR REPLACE SYNONYM "MyScheme"."MySynonym" FOR "MyScheme2"."SomeTable";</code> I created materialized view: <code>CREATE MATERIALIZED VIEW MyMView T...

SQL request with analytics to fill previous values

AskTom - Hot Questions - Thu, 02/01/2018 - 00:00
Hello, I have a table with this structure table values: <code>(code varchar2(10), date_value date, value number);</code> Example data : <code>'Code1','15/03/2017',25000 'Code1','06/06/2017',26000 'Code1','18/07/2017',29000 'Code1...

Pages