Sponsored Content
Top Forums Programming Transform SQL-Query to not use subqueries Post 302974948 by stomp on Monday 6th of June 2016 01:39:02 PM
Old 06-06-2016
Transform SQL-Query to not use subqueries

Hi,

I have a working SQL-Query here, in which I'm using subqueries to get the needed result.

The idea behind this, is to get a list of networks which have 2 tags set in the database. Because of the way it is stored, i get a tuple for each Tag, so I get a duplicate of all networks.

The Tables and the query:

Code:
Table: IPv4Network
  id,ip,name,comment

Table: TagStorage(network_id -> tag_id like 1 -> n)
  tag_id, network_id 

Table: TagTree
  id, tag

select ip,mask,name,tag
from IPv4Network 
inner join TagStorage         on     IPv4Network.id = network_id 
inner join TagTree         on         tag_id = TagTree.id 
where 
   ip in (select ip from IPv4Network 
        inner join TagStorage on IPv4Network.id = network_id 
        inner join TagTree on tag_id = TagTree.id where tag="AUTO_PROVISIONING") 
    and ip in (select ip from IPv4Network 
           inner join TagStorage on IPv4Network.id = network_id
           inner join TagTree on tag_id = TagTree.id where tag="SALES")
group by ip;

The result without the subqueries:

Code:
+------------+------+---------+-------------------+
| ip         | mask | name    | tag               |
+------------+------+---------+-------------------+
| 1111111512 |   26 | AUTO_01 | AUTO_PROVISIONING |
| 1111111576 |   26 | AUTO_02 | AUTO_PROVISIONING |
| 1111111640 |   26 | AUTO_03 | AUTO_PROVISIONING |
| 1111111704 |   26 | AUTO_04 | AUTO_PROVISIONING |
| 1111111512 |   26 | AUTO_01 | SALES             |      
| 1111111576 |   26 | AUTO_02 | SALES             |      
| 1111111640 |   26 | AUTO_03 | SALES             |      
| 1111111704 |   26 | AUTO_04 | SALES             |      
+------------+------+----------------+------------+

Subqueries are evil in terms of performance. The last time I worked with this, I wrapped it in some lines of code and sorted out the duplicates.

I'm wondering if there's not a more clever alternative in plain SQL, which is not such a performance killer than the subqueries.

Thanks in advance.

Last edited by stomp; 06-06-2016 at 02:48 PM..
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

& in SQL query

I have a script that looks for all jobs that contain a particular calendar. Some of the calendars have '&' in them and sql freaks out when it encounters that.. is there a way around this? I have tried: select job_name from job where run_calendar='1&15dom' select job_name from job... (3 Replies)
Discussion started by: Lindarella
3 Replies

2. Shell Programming and Scripting

rsh and sql query

Hi ... I am doing a switch user and then rsh and then running a sql query . I am successfull in rsh and logging into the database , but my query doesnt run .. Here's the command : su - linus -c "rsh -l linus psmf ORACLE_SID=SMP;export ORACLE_SID;sqlplus... (1 Reply)
Discussion started by: sars
1 Replies

3. UNIX and Linux Applications

SQL Lite query

Hello Everyone, I am looking to write a script that will run on many machines in a network at the same time. They need to write a result to a common location. I plan to use a SQLlite database as this common writing point. But the concern I have is how SQLlite will react to multiple writes that... (1 Reply)
Discussion started by: garric
1 Replies

4. Shell Programming and Scripting

sql query problem

Hi, I am passing an argument for the script and that argument values should exist in database. bill_period_input="'""$1""'" bill_period=`sqlplus uname/pwd@dbname <<eof! set verify off set heading off set feedback off select bill_period from bill_period_ref where... (4 Replies)
Discussion started by: ss_ss
4 Replies

5. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

6. Shell Programming and Scripting

create sql query

Hi Everyone, Can anyone pls help me out......with my requirement, i am struggling since 3 days. Please find the requirement below my file contains below data R1|Array/Network Resistor - VIP|V_RES_CLASS|V_MOUNT_FEATURE|SURFACE MOUNT|AND|8533.10.00.20|8533.10.00.20| R1|Array/Network Resistor... (9 Replies)
Discussion started by: jam_prasanna
9 Replies

7. Shell Programming and Scripting

problem in SQL query

I used the following code code select * from tablename where columnname Instead of printing the expected output it prints all the files in the present directory since there is a "*" in the code. Is there any way to overcome the problem? Thanks Ananth (2 Replies)
Discussion started by: Ananthdoss
2 Replies

8. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that... (2 Replies)
Discussion started by: Kapom
2 Replies

9. Programming

Getting error in sql query

Hi All , I have tried many times am getting syntax error on 'UNION' can anybody tell me ... INSERT INTO table1 ( Type , num_items , num_letters , total_value ) (select type='1', num_items, num_letters=count(*), total_value=sum(letter_value) from table2 where num_items = 1 (1 Reply)
Discussion started by: Venkatesh1
1 Replies

10. Programming

Help writing SQL query

Hello All, I hope I'm posting this in the right section. I have zero sql query writing skill, in fact, I've never done it before, but for some reason, a request came across my desk to get information from one of our databases. I have about 200 ticket numbers that have no information attached,... (8 Replies)
Discussion started by: bbbngowc
8 Replies
nvtagpg(8)						      System Manager's Manual							nvtagpg(8)

NAME
nvtagpg - Displays a disk block formatted as a tag directory SYNOPSIS
/sbin/advfs/nvtagpg [-v] tag_id /sbin/advfs/nvtagpg [-v] tag_id { page | -a } /sbin/advfs/nvtagpg [-v] fileset_id file_id /sbin/advfs/nvtagpg domain_id fileset_id -d dump_file /sbin/advfs/nvtagpg domain_id -d dump_file /sbin/advfs/nvtagpg volume_id -b block OPTIONS
Specifies that all the pages in the file be displayed. Specifies the logical block number of a disk block on an AdvFS volume. Specifies the name of a file that will hold the contents of the specified tag file. Display all the data in a specified tag file. OPERANDS
Specifies a tag file using one of the following formats: Specify only the domain to select and display the root tag file. The dump_file parameter is a previously-saved copy of the root tag file. You can use the -F option to force the utility to interpret the dump_file param- eter as a file name if it has the same name as a domain name. Specify the domain and the fileset to select and display the fileset's tag file. The dump_file parameter is a previously-saved copy of the fileset's tag file. You can use the -F option to force the utility to interpret the dump_file parameter as a file name if it has the same name as a domain name. Specifies an AdvFS file domain using the fol- lowing format: By default, the utility opens all volumes using block device special files. Specify the -r option to operate on the raw device (character device special file) of the domain instead of the block device. Specify the [-D] option to force the utility to interpret the name you supply in the domain argument as a domain name. Specifies an AdvFS volume using the following format: Specify the -V option to force the utility to interpret the name you supply in the volume argument as a volume name. The volume name argument also can be a full or partial path for the volume, for example /dev/rdisk/dsk12a or dsk12a. Specifying a partial path name always opens the character device special file. Alternatively, specify the volume by using arguments for its domain, domain_id, and its volume index number, volume_index. Speci- fies an AdvFS fileset using the following format: Specify the [-S] option to force the command to interpret the name you supply as a fileset name. Specify the fileset by entering either the name of the fileset, fileset, or the fileset's tag number, -T fileset_tag. Specifies a file name in the following format: Specify the [-F] option to force the command to interpret the name you supply as a file name. Specify the file by entering either the file's fileset relative pathname, file, or the file's tag number, -t file_tag. Specifies the file page number of the tag file. DESCRIPTION
The nvtagpg utility displays formatted pages of a root tag file or a fileset tag file. The utility also can save a copy of a tag file. Each AdvFS domain has a root tag file that lists all the filesets in the domain. Each fileset has a tag file that lists all the files in the fileset. Displaying the root Tag File The utility can display a page of the root tag file. For example, to display only the second page (which is page 1) of the root tag file in a domain named domain_1, enter the following command: # /sbin/advfs/nvtagpg domain_1 1 To display the first page of the root tag file (which is page 0), you can specify the page, as in the example above, or you can just name the domain, and the default display is page 0. If you use the default, the display includes the names of all the filesets in the domain. For example, to display page 0 of the root tag file and the names of all the filesets in the domain named domain_1, enter the following command: # /sbin/advfs/nvtagpg domain_1 Displaying A Fileset Tag File To display all the tags in a fileset, use the -a option. For example, to display all the tags in the fileset fileset_1 in the domain domain_1, enter the following command: # /sbin/advfs/nvtagpg domain_1 fileset_1 -a To display the tag entry for a file that has tag 9 in a fileset that has tag 2 in the domain domain_1, enter the following command: # /sbin/advfs/nvtagpg domain_1 -T 2 -t 9 Saving the Tag File To read the root tag file and save it to a file for later examination, use the -d dump_file option. For example, to save the root tag file from domain domain_1 to a file in the current working directory named save_domain_1, enter the following command: # /sbin/advfs/nvtagpg domain_1 -d save_domain_1 To save the tag file from the fileset fileset_2 in domain_1, enter the following command: # /sbin/advfs/nvtagpg domain_1 fileset_2 -d save_fileset2 Displaying Corrupted AdvFS Volumes When the disk structures are too corrupted to use the normal methods of viewing volumes, you can specify a logical block on a disk. For example, to display and format as a tag page a page that contains logical block 1024 on disk dsk5c (which is volume one in in domain domain_1), enter the following command: # /sbin/advfs/nvtagpg dsk5c -b 1024 Or, enter the following command: # /sbin/advfs/nvtagpg domain_1 1 -b 1024 NOTES
An active domain, which is a domain with one or more of its filesets mounted, has all of its volumes opened using block device special files. These devices cannot be opened a second time without first being unmounted. However, the character device special files for the volumes can be opened more than once while still mounted. It can be misleading to use this utility on a domain with mounted filesets because the utility does not synchronize its read requests with AdvFS file domain read and write requests. For example, the AdvFS can be writing to the disk as the utility is reading from the disk. Therefore, when you run the utility, metadata may not have been flushed in time for the utility to read it and consecutive reads of the same file page may return unpredictable or con- tradictory results. [The domain is not harmed.] To avoid this problem, unmount all the fileset in the domain before using this utility. RESTRICTIONS
The utility can fail to open a block device, even when there are no filesets mounted for the domain and the AdvFS daemon, advfsd is run- ning. The daemon, as it runs, activates the domain for a brief time. If the nvtagpg utility fails in this situation, run it again. EXIT STATUS
The utility returns a 0 (zero) on success, otherwise it returns a nonzero value and an error diagnostic. FILES
Specifies the command path. Specifies the AdvFS volumes in domain_name. SEE ALSO
Commands: nvfragpg(8), nvlogpg(8), vfilepg(8), vsbmpg(8) Files: advfs(4) nvtagpg(8)
All times are GMT -4. The time now is 12:54 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy