Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, santhoshreddy.

Asked: February 14, 2017 - 6:31 am UTC

Last updated: October 12, 2022 - 7:56 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,
I have two tables emp,grades without any constraints like primary and Foreign keys.My requirement is to give the grades to employees based on the grades given for salary range in grades tables.

i written a Join query for that but getting performance issue (cost is 5),i provided live sql link there you can see that.

So i would like to know is there any alternative to display the output in that format which i got using join query with out creating any relation using pk and fk?

with LiveSQL Test Case:

and Connor said...

Dont worry so much about the cost. Some joins can be done with fk/pk, and some joins cannot. That is just a fact of life when it comes to meeting the requirements of the business solution you are trying to achieve.

More important than the cost is the actual run time of the query. If the cost is small, but the runtime is hours - then your customers wont be too happy when you say "But the cost is really small" :-)

Your query looks fine to me. Inequality joins can be hard to optimize when the data volumes get big, but worry about that when the time comes.

(And thank you for providing a test case)

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 14, 2017 - 10:11 am UTC

is't there any alternative to this scenario?
Connor McDonald
February 15, 2017 - 3:45 am UTC

You could stick it all in JSON and use JSON queries to do it.


What is Json

Santhosh, March 30, 2017 - 7:21 am UTC

Would you mind Explaining about JSON i am not familiar with that, does that json Query works on Oracle DB?

sql

A reader, October 12, 2022 - 5:57 am UTC

hiii
my question is
i know the two tables and i will be find relationship between two tables

in sql devloper
Chris Saxon
October 12, 2022 - 7:56 am UTC

You mean the foreign keys?

Using the in-built data modeler you can get a diagram of the tables and how they're related.

Go to View -> Data Modeler -> Browser to see this, then open the Relational Models (right click New Relational Model) if there isn't one. You can then drag & drop tables from the Connections lists to see their properties and FKs.

If you want to "find" missing FKs, you can use this:

https://www.thatjeffsmith.com/archive/2015/08/discovering-foreign-keys-in-oracle-sql-developer-data-modeler/