DBA Blogs

Using function in conjunction with WITH query clause

Tom Kyte - Thu, 2018-10-04 22:06
Bit of a newbie, and hoping I can get pointed in the right direction. I've simplified things to demonstrate the issue I'm experiencing (and I'm really struggling to get a clear answer on other posts). When running the following: <code>with f...
Categories: DBA Blogs

SQL Query to Convert Ten Rows with One Column to Five Rows With One Column

Tom Kyte - Thu, 2018-10-04 22:06
i have a table with column name as value. There are 10 rows in the table. The desired output of this to be displayed as two columns first 5 rows as one column A and rows 6 to 10 as column B , next to each other as 5 rows of data like this <code>A B...
Categories: DBA Blogs

Calculate a variable date value and use it in a where clause to return all rows after that date

Tom Kyte - Thu, 2018-10-04 22:06
Long time SQL user of many flavors but brand new to PL/SQL and struggling to learn the "Oracle way". I've seen MANY examples of using variables in queries online and in documentation, but I've been unsuccessful finding a sample of what I want to do ...
Categories: DBA Blogs

SP execution plan should depend on input parameter

Tom Kyte - Thu, 2018-10-04 22:06
Hi guys, I have a SP having input parameters and the execution plan should depend on the parameters provided to the procedure. Ex : PROCEDURE GetData( DataType int, DataValue int ) I want this procedure to search DataValue in column1 if DataType =...
Categories: DBA Blogs

Understanding SQL Profiles

Tom Kyte - Thu, 2018-10-04 22:06
Hi Tom, My understanding of using SQL Profiles has always been that they would prevent (frequent) changes in access paths of SQL statement. This morning I noticed that, despite the fact that an SQL profile was connected to a statement and statias...
Categories: DBA Blogs

Understanding Distribution in #Exasol

The Oracle Instructor - Thu, 2018-10-04 04:12
Exasol doesn’t need much administration but getting distribution right matters

Exasol uses a clustered shared-nothing architecture with many sophisticated internal mechanisms to deliver outstanding performance without requiring much administration. Getting the distribution of rows between cluster nodes right is one of the few critical tasks left, though. To explain this, let’s say we have two tables t1 and t2:

The two tables are joined on the column JoinCol, while WHERE conditions for filtering are done with the column WhereCol. Other columns are not shown to keep the sketches small and simple. Now say these two tables are stored on a three-node cluster. Again, for simplicity only active nodes are on the sketch – no reserve nodes or license nodes. We also ignore the fact that small tables will be replicated across all active nodes.

Distribution will be random if no distribution key is specified

Without specifying a distribution key, the rows of the tables are distributed randomly across the nodes like this:

Absence of proper distribution keys: global joins

The two tables are then joined:

SELECT <something> FROM t1 JOIN t2 ON t1.JoinCol = t2.JoinCol;

Internally, this is processed as a global join which means network communication between the nodes on behalf of the join is required. This is the case because some rows do not find local join partners on the same node:

Distribution on join columns: local joins

If the two tables were distributed on their join columns with statements like these

ALTER TABLE t1 DISTRIBUTE BY JoinCol;

ALTER TABLE t2 DISTRIBUTE BY JoinCol;

then the same query can be processed internally as a local join:

Here every row finds a local join partner on the same node so no network communication between the nodes on behalf of the join is required. The performance with this local join is much better than with the global join although it’s the same statement as before.

Why you shouldn’t distribute on WHERE-columns

While it’s generally a good idea to distribute on JOIN-columns, it’s by contrast a bad idea to distribute on columns that are used for filtering with WHERE conditions. If both tables would have been distributed on the WhereCol columns, it would look like this:

This distribution is actually worse than the initial random distribution! Not only does this cause global joins between the two tables as already explained, statements like e.g.

<Any DQL or DML> WHERE t2.WhereCol='A';

will utilize only one node (the first with this WHERE condition) and that effectively disables one of Exasol’s best strengths, the Massive Parallel Processing (MPP) functionality. This distribution leads to poor performance because all other nodes in the cluster have to stand by being idle while one node has to do all the work alone.

Examine existing distribution with iproc()

The function iproc() helps investigating the existing distribution of rows across cluster nodes. This statement shows the distribution of the table t1:

SELECT iproc(),COUNT(*) FROM t1 GROUP BY 1 ORDER BY 1;
Evaluate the effect of distribution keys with value2proc()

The function value2proc() can be used to display the effect that a (new) distribution key would have:

SELECT home_node,COUNT(*) FROM (SELECT value2proc(JoinCol) AS home_node FROM t1) GROUP BY 1 ORDER BY 1;
Conclusion

Distribution on JOIN-columns leads to local joins which perform better than global joins: Do that!

Distribution on WHERE-columns leads to global joins and disables the MPP functionality, both causing poor performance: Don’t do that!

Categories: DBA Blogs

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part I (The Jean Genie)

Richard Foote - Thu, 2018-10-04 03:00
When it comes to indexing a partitioned table, many automatically opt for Local Indexes, as it’s often assumed they’re simply easier to manage and more efficient than a corresponding Global Index. Having smaller index structures that are aligned to how the table is partitioned certainly has various advantages. The focus in this little series is on […]
Categories: DBA Blogs

taking many days to merge clob column

Tom Kyte - Wed, 2018-10-03 09:46
Hi Tom, I have two tables which have clob data, Trying to merge the clob data from work table to Live table. its taking more and more days.. total data size 72GB. What is the best way to run the merge in this case to complete the task quickly. ...
Categories: DBA Blogs

What is the Oracle recommendation for Oracle RAC database and use of Sophos anti-virus software on LINUX?

Tom Kyte - Wed, 2018-10-03 09:46
What is the Oracle recommendation for Oracle RAC database and use of Sophos anti-virus software on LINUX? I have read nearly every Oracle document but can find nothing but Windows recommendation. Should i use that as a base for exclusions (? Oracle...
Categories: DBA Blogs

Calling a stored procedure to send a mail from after insert trigger

Tom Kyte - Wed, 2018-10-03 09:46
I have a requirement to send mail from an after insert trigger and I am passing the :NEW.MYID to the procedure. The procedure contains a query with aggregate functions and storing to an 'INTO mynvarchar2var' as a generated string to be send as email...
Categories: DBA Blogs

How to retrieve data from a quarter?

Tom Kyte - Wed, 2018-10-03 09:46
Right now am using the below query to extract the date. FROM c WHERE date <=TO_DATE ('30-SEP-18', 'DD-MON-YY'). Can you suggest me a way where I dont need to hardcode the date like 30-sep-18. Note: the example date is 30 sep 2018 because I'm ...
Categories: DBA Blogs

Fatal NI connect error 12170

Tom Kyte - Wed, 2018-10-03 09:46
Dear Tom, Please help to advice weather the below attachment is weather network error or not?? on our production database, lots of such TNS-12535: TNS:operation timed out errors. *************************************************************...
Categories: DBA Blogs

Foreign Key violation not happening

Tom Kyte - Wed, 2018-10-03 09:46
Hi, I have parent table X (Code table), and a child table CUSTOMER. I have a FK constraint enabled on CUSTOMER. But still database is accepting/inserting CDE in table CUSTOMER table which are not present in Table X. When FK Constraint is enab...
Categories: DBA Blogs

The Future of Partitioning

Tom Kyte - Wed, 2018-10-03 09:46
Hi Tom, The 11g release did nicely complete (nearly all) possible combination of composite partitioning schema. The missing hash - hash partitioning schema is probably not very interesting. The question is, does it mean that the development of ...
Categories: DBA Blogs

Migrating spatial data from SQL Server to Oracle

Tom Kyte - Tue, 2018-10-02 15:26
Hi, I would want to migrate spatial columns of data from SQL server to Oracle database server. Can you please provide how to begin. The sql developer tool does not support migration of spatial data. Could you provide appropriate tool/steps necessa...
Categories: DBA Blogs

Compressing LOB data increases table size

Tom Kyte - Tue, 2018-10-02 15:26
Hello, I think this a more conceptual question: In our core production database system, we have a pretty big table that weights 15,5TB. Of these, about 14.4TB are XML Data stored in a LOB column. We decided to reduce the size of the database...
Categories: DBA Blogs

Delete on billions record table

Tom Kyte - Tue, 2018-10-02 15:26
Hi Chris, Hi Connor, Appology for puttingy query in theoritical manner, but I would request you to please suggest something which I can try in below scenario. There is requirement when I need to cleanup a table (e.g. orders) having approx. 25 b...
Categories: DBA Blogs

Database Password Change

Tom Kyte - Tue, 2018-10-02 15:26
Hello Experts, I am just curious about changing passwords via cmd sql for SYS user. I issued the below command: alter user SYS identified by new_password; commit; Then restart the instance and OS. When I tried to use the new password, it ...
Categories: DBA Blogs

Regarding Undo tablespace

Tom Kyte - Tue, 2018-10-02 15:26
Which background process writes the copy of "old image" from "db buffer cache" to undo -segment of undo tablespace ?
Categories: DBA Blogs

Find the min date from a list of date without using a sub select query

Tom Kyte - Mon, 2018-10-01 21:06
Hi, I am working on a project and I need some help with the query. I have the following data in my existing table: <code> ----------------- S_ID S_DATE ----------------- A 01-FEB-12 A 14-MAR-12 A 28-APR-14 A 28-MAR...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs